Wednesday, November 14, 2018

Converting spool file data into data file

copy spool file data to physical file

I was surprised that I was asked this question, but as I have been asked six times in the last week it must be something that is important for people to find an answer for. Copying data from a spool file to a data file with separate fields is not an efficient way to get data. If you are considering using this method for capturing system information, spool files, active job info, etc, then you should be using the Views, Tables, etc that IBM has been creating for us to use. You can search this web site to see if I have written about getting to the information you desire using SQL. Trust me it is easier that what I am describing below.

In this example I am going to use the Work Output Queue command, WRKOUTQ, to generate a list of spool files, and I will be writing that data to a file. If I was doing this in the real world I would using the SQL and the method described in Output queue entries information via SQL. Now I have vented my feelings on this, let me proceed.

I want to know the following information about spool files in the QEZJOBLOG output queue:

Wednesday, November 7, 2018

Using new SQL built in function to convert character to number

using sql built in funtion to_number decfloat_format to convert alphanumeric number to decimal number

Included in the technical refreshes for IBM i 7.3 TR5 and 7.2 TR9 was a new Db2 for i built in function to convert character strings into numbers.

Why is this needed when I can just CAST the one type of a column to another?

01  SELECT COLUMN1,
02         CAST(COLUMN1 AS DECIMAL(10,2))
03    FROM QTEMP.TESTFILE

Numbers are problematic as I can make character representations of them in many different ways. I created a few examples of character representations of numbers, and then used the above SQL statement to display them.