More about getting information about SQL Views

Several years ago I wrote a post about the Db2 for i (SQL) Views about Views:

• SYSVIEWS:  Information about Views themselves
• SYSVIEWDEP:  Information about Views' dependencies

One of the things I talk about during presentations I give to Local User Groups, LUG, is the use of SQL Views and how they make my programming simpler. Therefore, I wanted to revisit these two Views and explain how they help me learn more about the Views in the partitions I use.

Before I can show what kinds of information I can get from SYSVIEWS and SYSVIEWSDEP I am going to need some Views to play with.

I am going to join a DDS file, TESTFILE, and a DDL table, TESTTABLE to create my first View. While it is not essential to know what the file and table look like I am just going to include their source code below.

First TESTFILE:

Generate spreadsheet with SQL in batch job

When I wrote about the Db2 for i, or SQL, scalar function to create a Microsoft Excel spreadsheet in the IFS my example program ran interactively. I recently received a message asking the following: "good stuff even though I started to use it, it has some limitations so far: doesn't work with QTEMP files. Am I right?"

I submitted my example program to batch and found that the expected spreadsheet was not generated. Looking in the job's job log I could see several messages related to files I created in QTEMP not being found. It would appear that the Java called by the GENERATE_SPREADSHEET submits a job to batch with the type BATCHI.

If I should not use files in QTEMP then I need a Standard Output file, STDOUT, in another library. I chose to create it in QGPL, but it could be in any library that is in the users' library list. As STDOUT is a source file I create it with the following:

CRTSRCPF FILE(QGPL/STDOUT) RCDLEN(266) + TEXT('Standard output file')

End to free format directive

What I call totally free RPG and what IBM calls modern RPG has been around for nine years. This type of RPG always needs the **FREE directive starting in the first position of the source line. And what follows is the free form RPG, where the source line can start in any position.

...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 01 **free 02 dcl-s Statement1 varchar(100) inz('Entered in free format RPG') ;

Marc Raynor informed me of another directive that "ends" the free format RPG, **END-FREE. After that directive all valid RPG must be entered in the sixth position for fixed format code, and later for the not completely free format code.

Having been told this I had to try it:

ACS 1.1.9.5 out now

 

The original contents of this page have become obsolete, go to this page for up-to-date information.

 

Using Select Into with Execute Immediate

The idea for this post came from a question I was asked about using the Execute Immediate SQL statement. The questioner asked why he was unable to execute a Select Into statement using Execute Immediate. His RPG source code compiled, but when called the program would error with a SQL code of -312, or message id SQL0312:

Variable RESULT not defined or not usable for reason code 8. 8 -- The host variable is specified in dynamic SQL. Host variables are not valid in dynamic SQL. Parameter markers must be used instead.

I found a solution for him. I could resist playing with what I found and created a far more complicated scenario that I am sharing with you in this post.

Send email with SQL in batch job

When I wrote about the Db2 for i, or SQL, scalar function to create a Microsoft Excel spreadsheet in the IFS my example program ran interactively. I recently received a message asking the following: "good stuff even though I started to use it, it has some limitations so far: doesn't work with QTEMP files. Am I right?"

I submitted my example program to batch and found that the expected spreadsheet was not generated. Looking in the job's job log I could see several messages related to files I created in QTEMP not being found. It would appear that the Java called by the GENERATE_SPREADSHEET submits a job to batch with the type BATCHI.

If I should not use files in QTEMP then I need a Standard Output file, STDOUT, in another library. I chose to create it in QGPL, but it could be in any library that is in the users' library list. As STDOUT is a source file I create it with the following: