Wednesday, December 12, 2018

Using a trigger to prevent the deleting of records

trigger to block delete of record in file

The idea for this post came from a question I was asked about ways to stop a programmer from deleting records from a file using the STRSQL command. I thought this would be a good reason to use a combination of two solutions I have written about before.

My first thought was to use a validation trigger. Validation triggers must be executed before the insert, update, or delete occurs. That way I can determine if the STRSQL program was used, and if it was return an error code to the calling program.

Validation programs can be written in any language, and I have written them in both RPG and SQL. In this scenario I will be writing the trigger in SQL as, in my opinion, it is easier to write and, more importantly, easier for someone else to understand what is going on.

Wednesday, December 5, 2018

Find String PDM to an outfile

fndstrpdm to an output file

We have all had those requests from our superiors: "Where is insert-name-here file used?"

There are tools from third parties that will give me this information. I have used some of them, and found that they missed some objects. I always combine the information these tools provide me with a scan of the members in the source files in the production library list. I use the Find String PDM command, FNDSTRPDM, to search for the string of information I want. Most people who use this command use it to produce a printed list of the source members it finds. While that may be OK if there are just a few libraries and source members, I work in an environment where there are 25 libraries in the production library list and many of these libraries contain multiple source files. I do not want to have to copy information from all of the generated spool files into a physical file that I can share with others, that would be too much work.

Looking at the help for the FNDSTRPDM I had a eureka moment, that would give me a way to write the name of the source member to an output file.

Wednesday, November 28, 2018

Using CL to get records from a file for display

using cl to chain records from file

I have been receiving a lot of messages asking for more examples using the CL programming language. For those of you who have been asking I hope you find this useful.

A few weeks ago I received a request for help from a reader, just a beginner in the IBM i world, with an assignment he had to accomplish just using CL. He needed to write a program, or programs, that would allow the entry an employee id, and the program would retrieve information from the employee file and pass it to the display file for display. For anyone with a little RPG experience this is a very simple task, and we could all write a RPG program to do this in a few minutes. Many IBM i programmers balk to do anything like this in CL, but programming is programming, and the same steps you would perform in RPG can be duplicated in CL.

Wednesday, November 21, 2018

Using command to zip and unzip files

zipping and unzipping data to the ifs

In the past I have written about zipping files in the IFS using Java jar and APIs, this week a colleague showed me a simpler way to do it using a couple of IBM i commands.

He explained that these commands had been introduced in IBM i 7.2, and can zip and unzip files in the IFS as well as files in, what I call, the native IBM i environment. With these commands the zipped/compressed files are called "archive files", therefore, IBM named them:


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?

02         CAST(COLUMN1 AS DECIMAL(10,2))

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.


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.


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