Wednesday, December 30, 2015

Searching for strings in source files, using RPG

fully free rpg

In my previous post, Searching for strings in source files, I included a sample program written in CL. The day it was published I received a message asking me: "Could that all be written in RPGLE instead of CL?"

The answer is: of course it can be. I thought this would be a good excuse to show how I could write a RPGLE program to do this, which would include subjects I have written about before. The RPGLE program will function in the same way the CL one does. The program is passed three parameters:

Wednesday, December 23, 2015

Searching for strings in source files

find string pdm fndstrpdm

I was recently asked how it is possible to search all the source members in QCLSRC to find which program called a particular Cobol program. I am sure most of you reading this will immediately shout "Find string PDM!", and you are right.

I thought that this would be a good excuse to explain the Find String PDM command, FNDSTRPDM, to those who are not familiar with it. And I will then give an example CL program to show of how I could search one or all of the source files in a library, which is really a reminder of things I have written about before: SQL, do loop, subroutine, changing name of spool files.

Thursday, December 17, 2015

Is IBM i 7.3 already out?

I was looking for the latest PTFs for IBM i 7.2 TR3 when I found SE6395 for the RPG compiler. What caught my attention was not the fact that this was an APAR for fully free RPG, it was the releases, see below.

Click on image to see larger version

Does this mean that there are already PowerSystems servers running IBM i release 7.3 out there? Perhaps there are a few out there in the user community testing a new release. It begs the question: could this mean that a new release is imminent? We will just have to wait and see what 2016 brings.

You can see the page for this APAR on the IBM web site here.

Wednesday, December 16, 2015

Almost everything you wanted to know about files and tables

systables systablestat dspobjd

When looking through my previous posts I realized I had not written about one of what I consider to be one of the most useful SQL Views, SYSTABLES. I have come to use this View any time I need to retrieve information about files or tables in my IBM i environment, I say that as it does not include the files in the IFS. It is not a new View, it has been around for many years and releases. I find that by using SYSTABLES I can get information a lot quicker than I can using the Display Object Description command, DSPOBJD. When I join SYSTABLES with the View SYSTABLESTAT I have found that I rarely use DSPBOJD any more.

SYSTABLES is found in the library QSYS2, and it contains a row for each table, physical file, view, alias, and logical file. I am not going to list all of its columns as IBM does a good job of it here. I am going to mainly use the short names in my examples to save space in the code below. The columns I have most useful are:

Wednesday, December 9, 2015

Output queue entries information via SQL

output queue entries

When I heard what was coming in IBM i 7.2 TR3 and 7.1 TR11 I was excited to learn that two of the new introductions would be a SQL View and SQL Table Function to list spool files in output queues. Retention of spool files has always been a contentious issue in all the IBM i shops I have worked. The task of managing spool files is a thankless task, but without it I am always surprised how much disk space is lost to spool files after a few months.

I always wanted a quick and easy way to identify the following information about spool files:

  • How old is the spool file?
  • How big is it?
  • Who generated it?
  • What job generated it?

Wednesday, December 2, 2015

Trying fully free RPG

fully free rpg

Thanks to the guys at RZKH.DE I was able to try the new fully free RPG this weekend. The latest Technology Refreshes to IBM i 7.2, TR3, and 7.1, TR11, finally brought what can only be described a completely free RPG. Even though "all free" RPG that came in IBM i 7.1 TR7, it was still restricted to be used between the eighth and 80th columns. With these TRs it is no limited by any column restrictions, and I can use any position from the first to the last of the source member.

I thought it would be a good idea for me to share my first impressions of what I have found using "fully free" RPG, covering what I think is good and not so good.

Wednesday, November 25, 2015

Getting Active Jobs data using SQL

active_job_info table function wrkactjob

With IBM i 7.2 released a whole lot of useful Views and Table functions that allowed us to gather, select, and view data that had otherwise only been available via a command or API. I have written about some of them, and today I am going to do so for another: ACTIVE_JOB_INFO, which basically gives me the save information as the Work With Active Jobs command, WRKACTJOB.

ACTIVE_JOB_INFO, found in library QSYS2, is a Table Function, therefore, it used in a slightly different way to a View. Perhaps the most obvious difference is that the Table Function has parameters, where a View does not.

Happy birthday RPG IV

This year contains a few significant anniversaries for RPG.

Today RPG IV has come of age, celebrating its 21st birthday. The first version of RPG IV came as part of OS400 V3R1, which was released on November 25 1994. Even though the code was still constrained by columns, the new Definition specifications (D-specs) was introduced, and I could now use variable names that were up to ten characters long. It also made it possible to use Date, Time, and Timestamp data types along with operations codes to be able to easily perform math with them. And I no longer had to use indicators for reads, chains, etc.

Friday, November 20, 2015

TR3 and TR11 available today

The latest Technology Refreshes for IBM i, TR3 for 7.2 and TR11 for 7.1, are available today. Both were announced last month, and you can learn what they contain here.

Technology Refreshes are delivered via PTFs from IBM. The PTFs for these TRs are:

Wednesday, November 18, 2015

Selective prompting of your CL commands

selective prompts ?? ?*

I have a number of CL programs I have written for myself to "speed up" things I do on a regular basis. For example, I work in an environment with multiple PowerSystems servers each with one or more IBM i partitions. If I need to move object from one to another I could type in the series of commands I use one at a time, find the parameters I need to change, enter the value I want into the parameter, and then press Enter. Being someone who likes to KISS (Keep It Simple Simon) I have developed programs were I use selective prompting on the commands to keep it simple.

What is selective prompting? It is when in a CL program a command is displayed prompted, and parameters can be seen and changed. I can have all the parameters displayed and changeable, or only some displayed and only some of those changeable. The other parameters remain hidden and unchangeable. It allows me to only display the parameters I want to, the ones I need to change. The screen shot below shows what I mean:

Wednesday, November 11, 2015

Getting information about User Profiles using SQL

sql view user_info

As audit season comes round again there are a standard set of reports I need to prepare for the IBM i I am responsible for. Amongst them would be: a list of all the user profiles with the date they last signed on, user profiles with *ALLOBJ authority, etc.

I would use the Display User Profile command, DSPUSRPRF, with the outfile option to create a file I could then use Query to extract the information I wanted from it. The problem was that the data in the file is static.

Introduced in, I believe, IBM i 7.1 is just what I wanted a View USER_INFO, in the library QSYS2, which contains lots of useful information about user profiles. I can now get the information I want directly from this View. I can also build Views of the information I want over this View.

Wednesday, November 4, 2015

Using Relative Record Number with data files in RPG

relative record number, rrn, in rpg using recno and inzpfm

Lately I have received a lot of emails and messages about using Relative Record Numbers, RRN, with data files in RPG programs. So many I have decided to write this post so I can refer people here rather than answer their questions individually.

Before I get started I just want to say I can think of no good business reason to use RRN to get and manipulate records from Physical or Logical files. The best I can recall the last time I wrote a program using them was way back in time in the late 1980s on an IBM System/36, one of the ancestors of the AS400 and IBM i. If someone asked me today to write a program using RRN I would ask them "Why?" and need a very good reason why.

Wednesday, October 28, 2015

Using SQL to get information about Job Scheduled entries


Am I the only one who has found it difficult to get information about what is in an IBM i's Job Scheduler?

I can use the Work Job Schedule Entries command, WRKJOBSCDE, to create a print out of the details. But I just want a quick and simple way to quickly and easily find what I want.

Someone at IBM must have been listening as the View SCHEDULED_JOB_INFO was added in IBM i 7.2. I can now easily get to, pick and choose the information I want using a SQL SELECT statement. I will give some examples of what I find helpful, and show you what you can do using this View.

Monday, October 26, 2015

IBM i and Open Source presentation

This is a presentation of a Webex made by IBM's Tim Rowe to the COMMON User Group last Monday, October 19 2015, about Open Source on the IBM i.

Wednesday, October 21, 2015

Everything you wanted to know about dates but never dared to ask, part 2

Date built in functions, BIF, %DAYS %MONTHS %YEARS %DIFF and %SUBDT

In part 1 of this series I covered what I consider to be the basics:

  • Date formats
  • Defining dates
  • Moving values in and out of dates

In this part I am going to be covering the RPG built in functions for Date data types I use.

Tuesday, October 20, 2015

Everything you wanted to know about dates but never dared to ask, part 1

date data type in rpg

The Date data type was introduced in V2R3, but it was not until the introduction of RPGLE in V3R1 could we finally make use of it. Over those years I have used the Date data type extensively in my RPG programs and DDS files. I am surprised to still find developers who will not use them, or find others who do not fully understand the functionality of this data type.

Rather than address each item I have found I thought it would be better to write about the Date data type and how I can use them in my RPG code. This will be spread out over several posts, as to cover all I want to do is too much for just one. Let’s start today with what I consider the basics.

Wednesday, October 14, 2015

A better way of sending break messages

qezsndmg api better than sndbrkmsg and sndmsg

I have never liked the Send Break Message command, SNDBRKMSG, as it is not possible to send the message to a user without knowing which work station they are at. There is an alternative: the Send Message API, QEZSNDMG. This API allows me to send a message to directly to the user, without caring where they are. It is the only API I can think of that has an user interface that can be used interactively, and the same API can be used in a program to run without the user interface.

Below I am going to describe how this can be used with the user interface, and then how to call the API in a program.

Wednesday, October 7, 2015

Determining the last Friday/first Monday in the month

sql dayofweek last_day function

Sometimes a coincidence happens where two people ask for similar things that make me think that their question could become the subject for a post on this blog. Someone, via the internet, asked me if it was possible in RPG to write a program that would calculate the last Friday for every month. The following week I received a request, at my work, to run a report on the first Monday of every month. When two things like this come together it is a good subject to discuss.

Over the years I have found many ways to determine the day of the week. I have used CEE APIs (CEEDAYS and CEEDYWK), calculations of varying complexity (especially in RPGIII), and one software package (I will not name) had a file that had a record for every date from 1900 to 2100 with fields containing various date formats, week numbers, and the day name. For this scenario I wanted something simple, you know KISS (Keep It Simple Simon).

Tuesday, October 6, 2015

IBM i 7.2 TR3 / 7.1 TR11 announcement video

This is Tim Rowe’s, Business Architect for Application Development for IBM i, 7.2 TR3/7.1 TR 11 announcement webcast to COMMON Europe on Monday.

Monday, October 5, 2015

IBM i 7.2 TR3 and 7.1 TR11 is announced

The waiting is over we now know what is in latest Technology Refreshes for IBM i 7.2 and 7.1. 7.2's TR3 and 7.1's TR11 appear to contain the same enhancements to the development environment. I am not going to list them all here, just the ones that captured my eye.

For RPG-ers the biggest change is finally releasing RPG from the last constraint of the fixed format. Prior to TR3/TR11 I could only code between the 8th and 80th columns. Now my RPG code can start in the first position and finish at the end of the line. The only prerequisite is that **FREE is entered starting in the first column before my RPG code can start in the first position.

Sunday, October 4, 2015

IBM i 7.2 TR3 and 7.1 TR11 being announced tomorrow?

V7R2 TR3 and V7R1 TR11

From what I can tell the announcement of the latest Technology Refreshes, TR11 for IBM i 7.1 and TR3 for 7.2, will be made tomorrow, Monday October 5, 2015.

Having found the developerWorks pages for these Technology Refreshes last month, I noticed today that they have been updated with the enhancements for DB2 for i (SQL). Even as I am typing this post I see updates being made to these pages.

Wednesday, September 30, 2015

Data Structure arrays are cool, but have some limitations

data structure lookup subarr xfoot

I have given examples of Data Structure arrays in other posts, and while I use them I have found a few frustrating gotchas. Data Structures arrays were introduced in V5R1, back in 2001. In my opinion they are a more mature approach to handling data within a Data Structure than multiple occurrence Data Structures. And it surprised me how few of my colleagues knew about them.

Coding them is simple, I just have to add the DIM keyword on the line that defines the Data Structure with the number of elements I desire:

End of Support for IBM i 6.1

Today, September 30 2015, marks the end of support for IBM i 6.1. It became available March 21 2008 and has been the second longest supported release of the operating system, for 7 years 6 months and 9 days. You can see a list of all the operating system releases with their dates here.

If you are still using 6.1 you need to think about moving to a later release, as further upgrades and PTFs not available for you.

IBM's notification of the end of support for 6.1 can be found here.

This leaves 7.1 and 7.2 as the only supported releases of IBM i.

Wednesday, September 23, 2015

Build Views and Views of Views

sql view of views

Have you ever had one of those "Aha!" moments when, as we say in England, the penny drops when someone says something and you are left thinking: "Why didn't I think of that?" I had one of those moments at the OCEAN user group technical conference during a presentation by Paul Tuohy on embedded SQL in RPG.

I have described before what SQL Views are. If you look at the object attribute it is a "LF", but it is not Logical file. Confused?

Tuesday, September 22, 2015

New TRs coming soon for IBM i ?

ibmi 7.1 tr11 and 7.1 tr11 coming soon

I was surfing through IBM's developerWorks website looking for information about a Technology Refresh, TR, when I noticed that pages for IBM i 7.1 TR11 and 7.2 TR3 are now on that website.

Monday, September 21, 2015

More proof that RPGIII is done

cpf6301 odt

On Friday I was asked if I could help with a compilation error for a RPGIII program. When the source was compiled it produced a CPF6301 message, see below, and the programmer could not understand what it meant or how to fix it.

  Message . . . . : Program PGM1 in library LIB1 is not created as
  CPF6301 received.
  Cause . . . . . : Compile terminated in phase QRGRT at MI instruction 
  number '00000'X with message CPF6301. Text for message is: ERROR IN 

Wednesday, September 16, 2015


lookup %lookup

I am sure most of us RPG developers have thought when a Built In Function, BIF, is introduced to replace an Operation code it would work the same way. Earlier this week I encountered a situation where it did not, where the %LOOKUP BIF can give different results to using the LOOKUP operation code.

The Look Up Operation and BIF are used to find a value in an array. I had a program where I was adding elements to an array and using %LOOKUP to determine the first unused element in the array, which I could then use to determine the number of used elements in the array. The program passed testing and was deployed on to the production IBM i server. When being used in the production environment the program stopped being able to determine where the first unused element was. After plenty of head scratching and debugging the code I finally determined the cause of the problem, the %LOOKUP.

Wednesday, September 9, 2015

Putting the SQL options into the source

sqlrpgle set options commit

For many releases of IBM i we have been able to put keyword in the H-spec/Control options that are the same as the parameters in RPG's compile options, see here. The same is also available in SQL for RPG objects with embedded SQL, SQLRPGLE.

I have given some examples in the past of using the SET OPTION statement in SQL to turn off commitment control. There are a multitude of other options that mirror options in the SQLRPGLE compile commands. I am not going to list all of the options in this post as IBM provides them all on their web site, there is a link at the bottom of this article to the page. I am only going to show what I think are the most useful.

Friday, September 4, 2015

Video: Node js, DB2, and RPG talking at last

This video was produced by COMMON Europe of a presentation given by Aaron Bartell on how Node.js can interface with DB2 and RPG on the IBM i.

Wednesday, September 2, 2015

Having a timeout on screen

dspf time out invite waitrcd maxdev

The germ for this post came from a comment made by Glenn Gundermann on last week's Display screens of results without having to press Enter:

How would you do it for the times you don't want to lock the keyboard? You might want to have a screen being displayed to allow the user to do actions but if they don't, perform an action after a timeout.

After a bit of playing I have a solution that will have a screen time out if someone does not press a key in a certain amount of time. This is just simple example to demonstrate the method I found. In the example the user will be presented with a screen they can either press Enter, F3 to exit, or do nothing. If they do nothing after two seconds the program will proceed and display a second screen.

Wednesday, August 26, 2015

Display screens of results without having to press Enter

dspf lock rcvf

I received a message asking me if there was an easy way in RPG to have a screen constantly update without the user needing to press a key. The idea is that as the program progresses it would write a message to the screen to inform the user of what is happening. Fortunately there is, otherwise I would not have be written this post.

First we need to understand what RPG's operation code Execute Format, EXFMT, does. Simply put it is a combination of a Write operation of the record format to the workstation, followed by a Read operation to retrieve the input from the record format. I can easily replace the EXFMT in any program with a WRITE followed by a READ. If I want to just write a display file's record format to the screen I could just perform a write, but how can I stop the user for using the keyboard while the record format is displayed?

Monday, August 24, 2015

Speeding up CPYF, with selection criteria

cpyf fromrcd(*start) versus fromrcd(1)

Charlie Despres posed a good question in a comment to my original post Speeding up CPYF, "Does this still work if I add conditions to the CPYF?" I assumed he would see the same benefit, but there is only one way to really know, test it!

I used the same file, BIGFILE, and only copy records into the output file, QTEMP/@BIGFILE, where the value of the field FLD1 is in the range of 1,000 to 2,000. There are 12 records in the 1 million records in BIGFILE that fit the criteria.

The only difference from the original programs shown in my last post was the Copy File command, CPYF, statements. This statement, with the default of FROMRCD(*START), was placed in one program:

Wednesday, August 19, 2015

Speeding up CPYF

cpyf fromrcd *start and control blocking *buf256

Every developer type person who has worked on the IBM i, or its predecessors, has had to use the Copy File command, CPYF, to copy data from one file to another. Most have rarely used more that the first screen's parameters, thereby missing a way to make the command execute faster. For small files the difference is negligible, but in a file of several 100,000 records or more the method I describe below will make a noticeable difference.

I have to thank Stu Haddock for reminding me of this. It is all down to the value of the FROMRCD parameter of the CPYF command.

Monday, August 17, 2015

Investigating about the future of IBM i

Last month the Swedish publication Data3 published an interesting article about the future of our beloved plaform. Two students working at the Handelsbanken AB were asked to investigate the IBM i and its future. They interviewed developers, technicians, and managers from the bank and other companies, asking them about the platform and how RPG compared to other servers and programming languages.

Their conclusions were very positive for both the IBM i and RPG:

Wednesday, August 12, 2015

Using CL procedures

cl procedure callprc dclprcopt

All of the examples I can find of using procedures on the IBM i are mainly in RPG, with a few in C. This made me want to demonstrate that it is possible to create procedures in CL, and to call procedures using CL programs and procedures.

This is also a time to move from using "CLP" source members to "CLLE", as there are commands that will be used in this post that are not supported by old "CLP" source or "CLP" programs.

Tuesday, August 11, 2015

Free your thinking, free your code

rpg free format or all free

I have been surprised, and a bit disappointed, to find many examples of RPG code in various social media groups still written in columns. This seems to perpetuate the stereotype of the "AS400" and "RPG programmers" held by many others who do not work with the IBM i, that we are working with an out of date language on an out of date platform.

The reality is different. IBM has spent many hundreds of millions of dollars on the IBM i operating system and PowerSystem servers to make this a modern platform and market leader, which can do so much more than the AS400 could. Part of these enhancements has been the introduction of free format RPG.

Wednesday, August 5, 2015

Using the Error Subfile for messages in display files

errsfl with errmsg, errmsgid including values and chkmsgid

I recently received a communication asking me if there was an easier way to display messages on a display file than using a message subfile. In my opinion there is a much simpler way using the Error Subfile, ERRSFL, keyword in the display file.

I have been using the Error Subfile for many years, so long that I cannot remember when I first encountered it. By using it I do not have to add all the code for the message subfile to my programs. I can just enter ERRSFL into my display and let the operating system do the hard work.

Wednesday, July 29, 2015

Checking for locked objects in QDLS and IFS

object lock qdls ifs dspdlonam wrkobjlck qp0fptos

In the past week one of my established jobs has started an "object locked" error. After some quick analysis I discovered that the object in question is a file in the QDLS file system. This left me asking myself a couple of questions

  1. How can I see what is locking an object in QDLS?
  2. Can I do the same for an object in the IFS?

Wednesday, July 22, 2015

Data Structures in CL

data structures in CL using defined variables

Data structures have always been a useful part of RPG, especially when returning information from a procedure. In CL I can create "Defined Variables", which are the equivalent of a RPG data structure. This becomes useful when I have a CL procedure that is called by RPG procedure, or vice versa. I will explain that in detail in a future post.

I am sure we have all encountered a situation where a data structure is passed to a CL program, and I have seen many programs were the subfields are broken out into individual variables using the Substring command, %SST. While this is easy if the data structure contains only character subfields, it can get messy if there are numeric subfields as I have to determine the start and end positions while considering the packing of the number.

Wednesday, July 15, 2015

Read operation code extenders

read operations reade readpe readp readc chain and operation code extenders

Operation code extenders on a Read operation in RPG are those letters that are follow the operation code in parentheses. I am sure the two most commonly known ones are:

  read(e) TESTFILE ;
  reade(n) (KeyField) TESTFILE ;

Each one provides a little bit extra functionality that compliments the Read operation. On performing research for this post I found that with free format Read operations a number of additional extenders were added, some are available with only some of the Read operations in V5R4 and later releases.

Wednesday, July 8, 2015

The best way to find what is using all the disk space

disk space rtvdskinf prtdskinf

Almost by accident I have started discussions on how to determine the largest objects on your IBM i server. I have given examples of how:

  • Identify the 250 biggest objects using the DSPOBJD command, followed by a SQL statement, see here.
  • Retrieve the number of deleted records/rows in files/tables, see here.

Wednesday, July 1, 2015

Discovering the number of deleted records in a file

systablestat in qsys2

In last week's post, here, I gave an example of how I determine the 250 biggest objects on my IBM i server. I created the information about the objects by using the Display Object Description command, DSPOBJD, and mentioned if anyone knew of a better way to get the same information to contact me.

Wednesday, June 24, 2015

Select the number of rows from SQL's result set

sql select with fetch rows only dspobjd

There are times when I do not want all of the rows/records from a table/file just, for example, the 100 with the largest value in a variable. I could use a "Copy File" command, CPYF, to copy on the first 100, but I cannot sort with a CPYF to select the largest. Fortunately SQL's Select does offer me a way to do this.

Today did not start well as the development server reached critical storage, 93%+ of DASD was being used. After doing some basic deleting and purging of a few files I was not able to lower the percentage of DASD more than 1%, which was not enough. What I need to do was to identify the largest object on this IBM i and determine if they could be deleted or their contents purged. But how to find those big objects?

Wednesday, June 17, 2015

Overriding control block size using SQL

override_table is like ovrdbf but is used in sql

Last week I discussed how to use control block size in the "Override with database file" command,OVRDBF, see here. In this post I am going to introduce the SQL procedure OVERRIDE_TABLE. Unlike the OVRDBF command this procedure can only do one thing, set the control block size for a table/file.

The OVERRIDE_TABLE appears to have introduced in IBM i 7.1 TR7. It is not mentioned in the IBM i 7.1 KnowledgeCenter website, but is in the 7.2 site.

This procedure has only three parameters:

Wednesday, June 10, 2015

Using control blocking to improve database performance

ovrdbf control blocks to speed up copy files

IBM i 7.1 TR7 saw an addition to the "Override with data base file" command, OVRDBF, the ability to set the control block size when performing file I/O. By increasing the control block size I can increase the memory allocated to the file's I/O buffer, which will increase the speed of I/O to the file. The enhancement is to the second element of the "Limit to sequential only" parameter, SEQONLY.

Limit to sequential only:
  Sequential only  . . . . . . > ____        *NO, *YES
  Number of records  . . . . . > _________   Number, *BUF32KB, *BUF64KB...

Wednesday, June 3, 2015

Using SQL to get information from Job Logs

getting job log data using sql using joblog_info instead of rcvmsg

I know I have written a lot recently about SQL, but IBM has added a lot of great views that allow you to access information easily. A good example is today's post: how to get information from a job log using SQL. Prior to this if you need to get information from the job log you had to use the RCVMSG command or the "List Job Log Messages" API, QMHLJOBL. Now I can get the information I want just using a SQL Select statement.

The JOBLOG_INFO table function was added to IBM i 7.2 and 7.1 TR9. It returns one row for each job log message, and what I really like about it is I can choose what information to return by using a WHERE clause in the Select statement.

Monday, June 1, 2015

Analyzing recursive data with SQL

This presentation was made by Birgitta Hauser, IBM i SQL expert, to Common Europe on May 28.

Friday, May 29, 2015

New TRs available today

technology refresh tr2 tr10

The latest Technology Refreshes for IBM i, TR2 for 7.2 and TR10 for 7.1, are available today to download. You can learn more about them from IBM's developerWorks web site here.

You can learn more about what is in these Technology Refreshes from these posts:

Wednesday, May 27, 2015

Getting information about Views and Indexes

sql views sysviews sysviewdep sysindexes sysindexstat

We have been given a number of SQL views that allow us to harvest information from our IBM i's, and many are unaware of their existance.

With the move from DDS files to SQL tables, indexes, and views I needed to update my toolset to be able to list information about the last two in a quick to run and easy to read way. Fortunately I can use some of these views to do so:

Wednesday, May 20, 2015

Creating derived columns in SQL View

create view case column

A SQL View is a virtual table. Its columns can come from one or more SQL tables or DDS files. The data in the View is not stored in its own database object. When the View is used the Select statement is executed to produce the data. You can also create, what I call, "derived" columns. The "derived" columns do not exist in the original tables/files, they are calculated when the Select statement is run.

In the examples I am going to show how I can code "derived" fields in a View to:

Wednesday, May 13, 2015

Converting DDS files to SQL DDL using SQL

file dds to sql ddl conversion using sql generate_sql

In my last post I wrote about how to convert DDS files to SQL DDL using the QSQGNDDL API, you can read it here, and I mentioned that in IBM i 7.2 and 7.1 TR9 there was an alternative, the GENERATE_SQL SQL procedure.

Having used both I prefer GENERATE_SQL as it is simpler to use, just one statement in STRSQL, and it has the ability to use wild cards for mass conversions. Many of its parameters are in English, which makes it easier for others to understand my code. The has six mandatory parameters:

Wednesday, May 6, 2015

API to convert files to SQL

convert dds to sql table view index using qsqlgnddl

As part of the IBM i 7.1 release IBM notified us all of their intentions not to perform any future enhance to DDS for data (physical and logical) files, and are encouraging us to migrate those files to their equivalents in SQL (tables, views, and indexes). For many of us this is an overwhelming proposition as the applications we support contain many thousands of files. To convert these by hand would take a very long time. Fortunately IBM has an API, 'Generate Data Definition Language' (QSQLGNDDL), to assist.

Wednesday, April 29, 2015

Coping with data decimal error when read file

data decimal error pssr fixnbr and monitor group

I am sure we have experienced running a program when it errors with "Decimal-data error occurred", RNQ0907. After analyzing the program I would find that the error is caused by bad data in a field in the input file. Yes, I will correct the bad data, but I might want to have the program continue using a default value and create a dump that can be used for analysis later. Examples of creating dumps can be found in the post: Producing a dump from programs.

How can I flag the record as having an error, give the numeric field a default value, and continue processing?