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.

Wednesday, October 31, 2018

Easiest way to search job descriptions

easy way to get information from job description using sql view

I have written about getting the library list from a job description using the QWDRJOBD API. An API is fine for doing things for one job description, but when I want to search all job descriptions that, for example, contain a library in their library list it is not easy to do with an API. A SQL view would be ideal for this.

IBM has come to rescue and I can now retrieve data from job descriptions using the SQL view JOB_DESCRIPTION_INFO. It is listed as one of the enhancements added with the latest round of IBM i Technical Refreshes, 7.3 TR5 and 7.2 TR9, but I have found it on a instance of IBM i that has not had the latest TR applied.

The view contains every column you need to know about a job description. I am not going to list them in this post, as I want to show examples of the kinds of information I have been asked from get from job descriptions in the past. If you want to see all the columns that are available there is a link at the bottom of this post to the IBM KnowledgeCenter page describing this view.

Wednesday, October 24, 2018

Extracting parts of date and time using a SQL function

enhanced extract for getting information from dates, times, timestamps

Another of the enhancements with the latest round of Technical Refreshes, TR5 for IBM i 7.3 and TR9 for 7.2, is to the Db2 for i Extract function. This function will retrieve parts of dates and times from date, time, and timestamp variables. Prior to these TRs I could extract just basic information from the date (year, month, day) or time (hour, minute, second).

The enhancements allows me to retrieve a whole lot more information than I will ever need to know about a date or time.

Let me start with a SQL DDL table I built to contain the data for my examples:

Wednesday, October 17, 2018

SQL NOW and playing with timestamps

using sql to get the timestamp

This post started off as one thing and quickly morphed into something more. I was going through all of the additions and changes in the new Technical Refreshes for IBM i 7.3 and started playing with the Db2 for i (SQL) NOW built in function.

NOW returns the current timestamp, and it has been around since at least 7.1. The latest TR allows me to give the decimal precision (fraction) of the seconds, for an example see below.

  2018-10-17-18.26.32.206964

As I said now I can give NOW the decimal precision I want from zero to twelve.

Wednesday, October 10, 2018

Removing multiple alpha characters using SQL

using sql translate function to translate characters

I needed to remove alphabetic characters (A - Z) from a field in a file, and left justify the remaining numeric characters. These alpha characters could be before and/or after any numbers contained within the string. As usual I need data in a test file, TESTFILE, that I can play with to find a way to do what I want.

In these examples, as the subject I have only bothered with two records/rows in my table/file. Why have more when I know what works in these examples will work in any scenario.

COLUMN
abc1234defghijklmnop
aaBbcCDd1mmg2k3LLkkH

The problem is that I want to replace any alpha character, no matter where in the string and what order they come in.

Friday, October 5, 2018

What's this thing called IBM i

A video from IBM Champion Trevor Perry explaining what IBM i is, and what is its future.

Video published April 20, 2017

Wednesday, October 3, 2018

Creating uniqueness using rowid

rowid column in sql table to ensure record uniqueness

The Db2 for i manual offers using a row id column, ROWID, as an alternative to an Identity column for ensuring table row uniqueness. While I wrote about using a ROWID SQL data type in RPG, I did not describe how the ROWID works when defined as a column in a table.

ROWID can only be used in SQL DDL tables, I cannot add it to a DDS file, using ALTER TABLE. It is defined in a similar manner to an Identity column where I say that the columns contents are generated by Db2 for i automatically.

For example, here is the DDL table I will be using:

01  CREATE TABLE MYLIB.TESTTABLE
02  (COLUMN1 CHAR(3) NOT NULL,
03   COLUMN2 CHAR(10),
04   COLUMN3 ROWID GENERATED ALWAYS IMPLICITLY HIDDEN
05  )

Wednesday, September 26, 2018

SQL type variables in RPG

sqltype variables used when dcl-s in rpg

I have written about defining RPG variables defined as SQL types in past posts, which made me curious about all there are. Having searched in IBM's Knowledge Center I was disappointed to find that there was no one page with all of this information upon. Which gave me idea to write this, a list of all SQL types available.

If I am defining variables as SQL types my source needs to be compiled as a SQLRPGLE, as the RPG SQL precompiler converts these SQL data types into more familiar RPG equivalents.

The syntax for these variable definitions is simple, it is just:

Thursday, September 20, 2018

Closing all the files with one operation in RPG

closing multiple files with one rpg close operation code

There are times I stumble across things in the IBM manuals I felt I should have known. One of these is the ability to close all the files in a program with just one close statement. I have no idea how old this ability is, but it is relevant if you are programming your RPG in a modern manner.

Before main procedures were introduced to the RPG world, our programs were simple. We defined our files, and knew that they would close when the program ended.

01  **free
02  ctl-opt option(*nodebugio:*srcstmt:*nounref) ;

03  dcl-f PFILE1 ;
04  dcl-f DSPFILE workstn ;
05  dcl-f PRTFILE printer ;

06  *inlr = *on ;