Automating query

coronoahcoro
coronoahcoro used Ask the Experts™
on
I have some queries that I have to run manually every month and I would like to know if there is any way to automate this especially I don't want to have to change the date on every query one by one. I don't know much about AS400 other than running the query and making record selection but I know a bit of Visual Basic, is this something that I can do through Visual Basic?

Please advise and since I do not know much about AS400 please give me the actual step on how to do this. Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Molly FaganApplications Team Supervisor

Commented:
You can use VB to query AS400 data--I do it all the time.  If you use ODBC, you'll need to make sure that you have the iSeries drivers installed on your development machine as well as the machine that will be running the app.  Otherwise, I just use OleDB connection strings.

Author

Commented:
could you please give an example of how to get the data using VB?
Applications Team Supervisor
Commented:
First of all, you do need to have the iSeries drivers even if you're using an OleDB connection--sorry about the confusion.

Here's an example of running a delete query.  You have said what you're wanting to do--like return information to be dumped somewhere else, etc.  But basically, you're going to have to use in-line SQL to run queries on the AS400 (the 400 programmers were I work do not use stored procedures and I haven't been able to find anyone who does to be able to show me how to do it--I write programs hitting both SQL Server and the AS400 and use stored procedures on the SQL Server side of things).


Dim gpdb As New OLEDB.Connection()
        gpdb.Open("Provider=IBMDA400.DataSource.1;Data Source=SERVER_NAME;User ID=userid;Password=password;Default Collection=LIBRARY_NAME")
        gpdb.Execute("DELETE FROM FILENAME")
        gpdb.Close()
        gpdb = Nothing

Open in new window

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Gary PattersonVP Technology / Senior Consultant
Commented:
When you say you "Have some queries", what do you mean?  SQL queries, or Query/400 (WRKQRY) queries?  How do you currently run them?

- Gary Patterson

Author

Commented:
Query/400. Currently I run them manually with WRKQRY cmd then export the result to Excel with the Add-in that comes with the iSeries package
Gary PattersonVP Technology / Senior Consultant

Commented:
What data changes do you need to make?  Just change the date to the current date, or is it more complex?

Commented:
You have 2 options:
1. you can use configure the query in excel with VB, and have the excel run the query on the AS400 and refresh the data.
2. On the AS400 you can call the query from a program, using the program to change the date parameters. This assumes that the date in the query is a function the the date (last month)

Author

Commented:
@Gary: for now I would like the program to be able to change the date on the query to the date of previous month, run the query, and give me a result. So let's say now is August I would like the program to be able to change the date in the query to something like:

INVDT RANGE 20110701 20110731

so I will get the date from July, then it will give me results
Gary PattersonVP Technology / Senior Consultant
Commented:
If you have good SQL skills, this isn't too tough.  First trick is to get this query out of Query/400 and into Query Management Query (SQL), since RUNQRY does not allow you to programmatically specify selection criteria at run-time.  Here is a nice article that explains the process:

http://www.itjungle.com/fhg/fhg052604-story01.html

Now you have an SQL query.  The next step is to modify the selection criteria (WHERE clause) to select only records from the previous month based on today's date.  

This is pretty easy in concept, but the syntax is a little tricky.

Take today's date (Current_date in SQL), and subtract one month.  Compare the "Year" and "month" portion of this date to the "Year" and "month" portion of each date in your file, and select those that match.  Based on your selection criteria above, I'm going to assume your dates are stored in an 8-digit numeric field called MYDATE in yyyymmdd format.  I didn't test this, but it is very close:

WHERE (SUBSTRING(DIGITS(MYDATE,1,4)) = CHAR(year(current_date - 1 month)) AND (SUBSTRING(MYDATE,5,2) = CHAR(month(current_date - 1 month)))
DIGITS converts your numeric date to a string
SUBSTRING pulls out the year or month portion of the now-string date.
YEAR and MONTH functions pull out the year and month portion of the date resulting from (current_date - 1 year/month)

The nice thing about this query is that it doesn't require you to modify it each month, so you can just compile it  (CRTQMQRY) and run it STRQMQRY, and you don't need to build a CL or other program to calculate your month ranges for it.  SQL date subtraction also handles year-end boundaries, long/short months, leap years, etc.

- Gary Patterson



Gary PattersonVP Technology / Senior Consultant

Commented:
You mentioned that you aren't too familiar with the AS/400.  Get someone that is to help you modify the query (it will be stored in a source member and can be edited using whatver programmer editor you prefer: SEU, Rational, etc.).

SEU has excellent built-in help.  The STRSEU command can be used to start SEU and load the source member you created that contains the QMQRY source code.

Once in the editor, position the cursor in various areas of the screen and press F1 for context-sensitive help.

- Gary Patterson
Technically, Query/400 does allow you to specify external variables such as dates. There are a couple considerations.

First, the query must be defined as a "dependent" query. The given date or other selection value is then a "dependent value".

But then, the RUNQRY command cannot run the query unless the RCDSLT(*YES) parameter is used and the dependent value is supplied that way. Unfortunately, that's usually not what's needed since that's an interactive action.

So, instead of RUNQRY, the STRQMQRY command must still be used with ALWQRYDFN(*YES), and the dependent value is supplied with SETVAR() just as a normal QM query would be.

Of course, it's likely that the better course is to convert the queries to QM queries and simply give up the old Query/400 product entirely.

Tom

Author

Commented:
Sorry guys, I'm still trying to understand this. I never worked deep with AS400 so this is all new to me. I still don't understand the function of sourcemember (QDDSSRC), etc. Any good source that I can read to understand the basic of AS400?
Gary PattersonVP Technology / Senior Consultant

Commented:
A "source physical file" (or source file) is a specialized DB2/400 database file designed to hold source code for programs, scripts, text, compilable SQL statements, etc.  Source physical files usually contain multiple sub-components called "members".  Each member typically contains the source code for one object (a screen, a file, a program, a script, etc.).  Source physical files can be stored in any library, and can be named any way you like, but there are some very common standard names:

QDDSSRC (Data Description Source Code)
QRPGLESRC (ILE RPG Source Code)
QCLSRC (Command Language Source Code)
QTXTSRC (Text - often documentation)

To compare to a PC file system, the source physical file is sort of like a specialized folder that can only hold text files, but nothing else.  The source members are like individual text files containing source code.

So: MYLIB/QSQLSRC(MYSCRIPT) on the AS/400 refers to the source member MYSCRIPT in source file QSQLSRC, which is in library MYLIB.  

The equivalent on Windows might be:

c:\myfolder\SQL Source Code\MYSCRIPT.SQL

There are lots of good AS/400 primers (disclaimer: I wrote one of them, so I'm biased!):

http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords=iseries+and+as%2F400+operations&rh=i%3Aaps%2Ck%3Aiseries+and+as%2F400+operations&ajr=0

- Gary Patterson

"I run them manually with WRKQRY cmd then export the result to Excel with the Add-in that comes with the iSeries package . "

There is an Excel add-in that allows you to query i-series data direct. maybe you could use that instead of WRKQRY.
If I can find a link I'll add it.
Gary PattersonVP Technology / Senior Consultant

Commented:
In case ASmith_SBS can't find the links:

Excel Add-in for V5R4 and earlier: http://www-03.ibm.com/systems/i/software/toolbox/perfhints.html
Excel Add-in for V6R1 and later: https://www-304.ibm.com/support/docview.wss?uid=nas14921b1637a7c7e7f86257495006f8373

- Gary Patterson

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial