AS400/Monarch 15 Automation
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
------------------------------
Answers
-
Hi Al,
Most of the work to achieve this is done on the AS400, but you need to make sure Client Access is installed during the iNavigator installation - I don't think it is, by default. I'm afraid I don't have any knowledge of the steps needed to create a shared folder in the iSeries.
Once you can browse to the folder using Windows, you should be able to treat it in exactly the same way as a standard Windows file share.
As far as Automation, I'm afraid I need to defer once again to our friends on this forum that have AS400/iSeries experience for the IBM side. For the Monarch side, there are a couple of points to mention.
Firstly, the Standard edition of Monarch 15 does not support command line or com automation, but it can be enabled with additional licensing. If you have this enabled, then it is straight forward to run a command line through Windows scheduler.
"C:\Program Files\Datawatch Monarch 15\Monarch.exe" /rpt:"\\MyServer\MyResource\input.prn" /mod:"C:\Models\Rule_6754C.dmod" /exp:"\\MyServer\MyResource\Exports\MyFile.xls"
or you can use Projects
"C:\Program Files\Datawatch Monarch 15\Monarch.exe" /prj:"C:\Projects\Rule_6754C.dprj" /pxall
Automator would be a better option for true automation where the operation would run as a service on a Windows server instead of an end user's PC that must be turned on and logged in to use Monarch Automation.Be cautions of mapped drives. User A might have an X: drive mapped to the AS400, but user B might have it mapped as Y: and their X drive might point somewhere else. UNC paths are better.
Sorry I can't be of more help on the IBM side.
Regards,
Steve.
------------------------------
Steve Caiels
Professional Services
Altair
------------------------------
-------------------------------------------
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
------------------------------"0 -
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
------------------------------
"0 -
Hi Joseph / Steve: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
------------------------------
"
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
------------------------------
"0 -
Hi Joe: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
------------------------------
"
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
------------------------------
"0 -
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
------------------------------
"0
Altair Employee