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

I want to thank Shaker Steven for bringing to my attention this morning that the Spring 2024 update for Access Client Solutions, ACS, is now available. When I opened my ACS the following window popped up informing me the new update was available.

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.

Patron saint of IT's day

In the western Christian calendar today is a special day for everyone working in information Technology, as it is Saint Isidore of Seville saint's day.

St Isidore of Seville is special to the IT community as in 1997 he was made patron saint of all things related to computers and IT by Pope John Paul II. You can learn more about Isidore here.

Happy St Isidore of Seville day, and let's hope for a little divine intervention so all of our IT projects in the next year complete successfully, on time, and on budget.

"Looking at his example it looks so easy. Gratias tibi RPGPGM.COM!"

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: