AS400/Monarch 15 Automation

Joseph_21004 said:

Al,

In addition to Steve's remarks which are very good, I do use the Automator/AS400 process quite often in our company, and what I do using the Client Access software ,which has the proper ODBC Drivers needed to make a direct connection to the AS400. With having that properly configured, I create a DTF (Data Transfer File) in Client Access that has my query stored, so when I use Automator, I simply call in the Automator Pre-Script to run the DTF File and then the query runs against the AS400 and stores the file locally for Automator to pick up as a monitored file and run its process from there.

Another option you can use is if you have the IBMDA400 driver, you can create an OLEDB connection in Data Prep Studio and create the query that way as well, which is a little more robust, and relies only on the driver and Data Prep, not another application to run first.

One thing also to note, make sure that you have the AS400 username/password credentials properly stored! (I use the cwblogon.exe that comes with Client Access) also before the process in Automator runs, that way I know the credentials are properly stored. If they are not stored, it can lock you out of the AS400 after multiple failed attempts.

Hope this helps get you started!

Regards,

Joseph Territo

------------------------------
Joseph Territo
Senior Airfreight Analyst Americas
Dhl Global Forwarding
Miami FL
(786) 526-5306
------------------------------
-------------------------------------------
Original Message:
Sent: 10-04-2019 02:13 PM
From: Al Rice
Subject: AS400/Monarch 15 Automation

Hello All:

I am trying to build a Monarch Automation solution where the primary data source is the AS400, using data output from AS400 wrkqry(s) and spooled reports stored in iSeries Navigator.  Ideally, solution would be run on a scheduled basis on certain days, and times of day.  Has any of my trusted advisors in the forum has any experience with such automation and any advice about the do's and don'ts would be helpful.

Thanks

------------------------------
Al Rice
------------------------------
"

Hi Joseph / Steve:

Your response is timely, and you advice is very valuable.  While I have deep experience with Monarch, my knowledge of the AS400 is very limited.  I must rely on my client's AS400 support resource to make the AS400 file library and Navigator spool files visible and usable to Monarch ver. 15.x.

Your points about
(1) Client Access;
(2) proper ODBC Drivers;
(3) Automator Pre-Script to run the DTF File; and
(4) using use the cwblogon.exe to properly store username/password credentials 

are well noted to ensure reliable, ongoing connectivity to data files and spool reports using Automation (specifically using Command Line or VBA script with COM automation).  I will share these suggestions with my AS400 support source, as he/she is not familiar with the Monarch, and its automation capabilities.

Thanks for your input and I treasure this forum, and the Monarch community.

Al Rice

------------------------------
Al Rice
------------------------------
-------------------------------------------
Original Message:
Sent: 10-09-2019 11:01 AM
From: Joseph Territo
Subject: AS400/Monarch 15 Automation

Al,

In addition to Steve's remarks which are very good, I do use the Automator/AS400 process quite often in our company, and what I do using the Client Access software ,which has the proper ODBC Drivers needed to make a direct connection to the AS400. With having that properly configured, I create a DTF (Data Transfer File) in Client Access that has my query stored, so when I use Automator, I simply call in the Automator Pre-Script to run the DTF File and then the query runs against the AS400 and stores the file locally for Automator to pick up as a monitored file and run its process from there.

Another option you can use is if you have the IBMDA400 driver, you can create an OLEDB connection in Data Prep Studio and create the query that way as well, which is a little more robust, and relies only on the driver and Data Prep, not another application to run first.

One thing also to note, make sure that you have the AS400 username/password credentials properly stored! (I use the cwblogon.exe that comes with Client Access) also before the process in Automator runs, that way I know the credentials are properly stored. If they are not stored, it can lock you out of the AS400 after multiple failed attempts.

Hope this helps get you started!

Regards,

Joseph Territo

------------------------------
Joseph Territo
Senior Airfreight Analyst Americas
Dhl Global Forwarding
Miami FL
(786) 526-5306
------------------------------

Original Message:
Sent: 10-04-2019 02:13 PM
From: Al Rice
Subject: AS400/Monarch 15 Automation

Hello All:

I am trying to build a Monarch Automation solution where the primary data source is the AS400, using data output from AS400 wrkqry(s) and spooled reports stored in iSeries Navigator.  Ideally, solution would be run on a scheduled basis on certain days, and times of day.  Has any of my trusted advisors in the forum has any experience with such automation and any advice about the do's and don'ts would be helpful.

Thanks

------------------------------
Al Rice
------------------------------
"

Al_22614 said:

Hi Joe:

I have been working hard to complete my AS400/Monarch Automation.  As it turns out my client has a PC without sufficient capacity (memory, cpu, etc) to read in large data files via ODBC connection to the AS400.  So what we have done is create AS400 wrkqrys to extract the requisite data and store the output data in an AS400 library where Monarch can read the smaller files without any problems.

To execute the series of 14 wrkqrys, we set up 14 different AS400 macros to execute the wrkqrys and pass certain selection values to the wrkqrys.  What I need now is some help executing the AS400 using Monarch Auto Com automation which I have built in Excel.  My problem is trying to figure out how to trigger the AS400 macros from the VBA script in Excel.  I have search the internet for 5 days now, and the closest I have gotten to a solution is the VBA script below:

Sub RunAS400()
Dim DatMac As String
DatMac = "GLVENA.mac"
With CreateObject("Word.Application")
  If .Tasks.exists("Session") Then
   With .Tasks("Session")
    .Activate
    .WindowState = 1
    .Visible = True
'    Stop
    Run DatMac
   End With
  End If
 End With
End Sub

The above script does bring up the AS400 screen, but it errors when it gets to the "Run DatMac", which tries to execute the "GLVENA.mac" macro on the AS400.

Could you or anyone share some code that I make be able to edit to execute the AS400 macros.  I think I am very close now, and a little nudge will get me closer to the finish line.

Thanks
Al




------------------------------
Al Rice
------------------------------
-------------------------------------------
Original Message:
Sent: 10-09-2019 11:01 AM
From: Joseph Territo
Subject: AS400/Monarch 15 Automation

Al,

In addition to Steve's remarks which are very good, I do use the Automator/AS400 process quite often in our company, and what I do using the Client Access software ,which has the proper ODBC Drivers needed to make a direct connection to the AS400. With having that properly configured, I create a DTF (Data Transfer File) in Client Access that has my query stored, so when I use Automator, I simply call in the Automator Pre-Script to run the DTF File and then the query runs against the AS400 and stores the file locally for Automator to pick up as a monitored file and run its process from there.

Another option you can use is if you have the IBMDA400 driver, you can create an OLEDB connection in Data Prep Studio and create the query that way as well, which is a little more robust, and relies only on the driver and Data Prep, not another application to run first.

One thing also to note, make sure that you have the AS400 username/password credentials properly stored! (I use the cwblogon.exe that comes with Client Access) also before the process in Automator runs, that way I know the credentials are properly stored. If they are not stored, it can lock you out of the AS400 after multiple failed attempts.

Hope this helps get you started!

Regards,

Joseph Territo

------------------------------
Joseph Territo
Senior Airfreight Analyst Americas
Dhl Global Forwarding
Miami FL
(786) 526-5306
------------------------------

Original Message:
Sent: 10-04-2019 02:13 PM
From: Al Rice
Subject: AS400/Monarch 15 Automation

Hello All:

I am trying to build a Monarch Automation solution where the primary data source is the AS400, using data output from AS400 wrkqry(s) and spooled reports stored in iSeries Navigator.  Ideally, solution would be run on a scheduled basis on certain days, and times of day.  Has any of my trusted advisors in the forum has any experience with such automation and any advice about the do's and don'ts would be helpful.

Thanks

------------------------------
Al Rice
------------------------------
"

Problem solved. The IBM Pcomm library had all the answers. 

Thanks. 

------------------------------
Al Rice
------------------------------
-------------------------------------------
Original Message:
Sent: 10-23-2019 01:01 AM
From: Al Rice
Subject: AS400/Monarch 15 Automation

Hi Joe:

I have been working hard to complete my AS400/Monarch Automation.  As it turns out my client has a PC without sufficient capacity (memory, cpu, etc) to read in large data files via ODBC connection to the AS400.  So what we have done is create AS400 wrkqrys to extract the requisite data and store the output data in an AS400 library where Monarch can read the smaller files without any problems.

To execute the series of 14 wrkqrys, we set up 14 different AS400 macros to execute the wrkqrys and pass certain selection values to the wrkqrys.  What I need now is some help executing the AS400 using Monarch Auto Com automation which I have built in Excel.  My problem is trying to figure out how to trigger the AS400 macros from the VBA script in Excel.  I have search the internet for 5 days now, and the closest I have gotten to a solution is the VBA script below:

Sub RunAS400()
Dim DatMac As String
DatMac = "GLVENA.mac"
With CreateObject("Word.Application")
  If .Tasks.exists("Session") Then
   With .Tasks("Session")
    .Activate
    .WindowState = 1
    .Visible = True
'    Stop
    Run DatMac
   End With
  End If
 End With
End Sub

The above script does bring up the AS400 screen, but it errors when it gets to the "Run DatMac", which tries to execute the "GLVENA.mac" macro on the AS400.

Could you or anyone share some code that I make be able to edit to execute the AS400 macros.  I think I am very close now, and a little nudge will get me closer to the finish line.

Thanks
Al




------------------------------
Al Rice
------------------------------

Original Message:
Sent: 10-09-2019 11:01 AM
From: Joseph Territo
Subject: AS400/Monarch 15 Automation

Al,

In addition to Steve's remarks which are very good, I do use the Automator/AS400 process quite often in our company, and what I do using the Client Access software ,which has the proper ODBC Drivers needed to make a direct connection to the AS400. With having that properly configured, I create a DTF (Data Transfer File) in Client Access that has my query stored, so when I use Automator, I simply call in the Automator Pre-Script to run the DTF File and then the query runs against the AS400 and stores the file locally for Automator to pick up as a monitored file and run its process from there.

Another option you can use is if you have the IBMDA400 driver, you can create an OLEDB connection in Data Prep Studio and create the query that way as well, which is a little more robust, and relies only on the driver and Data Prep, not another application to run first.

One thing also to note, make sure that you have the AS400 username/password credentials properly stored! (I use the cwblogon.exe that comes with Client Access) also before the process in Automator runs, that way I know the credentials are properly stored. If they are not stored, it can lock you out of the AS400 after multiple failed attempts.

Hope this helps get you started!

Regards,

Joseph Territo

------------------------------
Joseph Territo
Senior Airfreight Analyst Americas
Dhl Global Forwarding
Miami FL
(786) 526-5306

Original Message:
Sent: 10-04-2019 02:13 PM
From: Al Rice
Subject: AS400/Monarch 15 Automation

Hello All:

I am trying to build a Monarch Automation solution where the primary data source is the AS400, using data output from AS400 wrkqry(s) and spooled reports stored in iSeries Navigator.  Ideally, solution would be run on a scheduled basis on certain days, and times of day.  Has any of my trusted advisors in the forum has any experience with such automation and any advice about the do's and don'ts would be helpful.

Thanks

------------------------------
Al Rice
------------------------------
"