Wednesday, December 30, 2020

List journal inheritance rules with SQL

sql view to view journal inheritance

Having brought up journal inheritance in my previous post I thought it would be wise to follow that up with this post. This post describes how to view the journal inheritance rules in your IBM i partition.

Like the JOURNALLED_OBJECTS SQL View this View JOURNAL_INHERIT_RULES was introduced as part of the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9. It gives a list of all the libraries on your partition with the journal inheritance rules. The only library on the IBM i partition I use for writing these posts that has any inheritance rules is the library I wrote about in my previous post, MYLIB2.

Being a SQL View the statement to retrieve results from it is very simple. In its simplest form it is just:

Tuesday, December 29, 2020

List all of objects that are being journaled by a specific journal

view objectts being journaled by a journal

While this post is not going to be one of my longer ones, it is going to demonstrate another useful addition to Db2 for i within the latest round of Technology Refreshes, IBM i 7.2 TR3 and 7.3 TR9.

I often want to know which objects are being journaled by a particular journal. In the past I gave an example of how to do this using the Work Journal Attribute command, WRKJRNA. But this addition makes it so much easier.

The new SQL View JOURNALED_OBJECTS give a list of all objects that are related to the journal. This is not just the files, it also returns names of the journal receivers. This is something that my prior example did not do.

As this is a View I can list all of the objects that are involved with all journals:

Wednesday, December 23, 2020

More special options for RPG debugging

rpg debug special fields qrnu

Last week I wrote about a new way in debug of changing the value returned from a procedure in a RPG program. Having had some time to look around I have found some other of these special debug features. These others have been around for several releases, therefore, if you are not on IBM i 7.4 or 7.3 with the latest Technology Refreshes you might be able to use some or all of them.

These "special names for debugging" are:

Wednesday, December 16, 2020

Two new control option to help with awkward numbers

new expropts to handle numbers of blank and with thousand separators

Many of us handle data coming from non IBM i sources, whether it be files sent from other types of systems or from web applications. While RPG has strict rules of what can be done with numbers, other systems do not. In my experience it is not uncommon to receive blank numeric data, or a number that contain thousand separators. We have all come up with our own ways of handling this kind of issues, but it is nice to see that IBM have introduced a couple new expression options to make this easier for us to work with.

Both come as part of the latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9. The these additions have been added to the existing Expression Options control option, EXPROPTS:

Tuesday, December 15, 2020

How to manipulate the value returned from a RPG procedure

new debug control option to all changing of values returned from subprocedure

Sometimes it can be hard in testing to change values returned from procedures. I don't want to change the code in the procedure. But how can I ensure that the returned value is what I want? In debug I can change the value of a variable, and this will change the change value returned if the variable is returned from the procedure. What can I do if returned value is hard coded? I cannot change that.

  return *on ;

Fortunately this is no longer an issue due to new enhancement added to RPG as part of the latest Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9.

There is a new control option value that can be inserted into the DEBUG keyword.

Wednesday, December 9, 2020

SQL to read Data Queue

One of biggest complaints I have always had about using data queues is that there is no easy way to see what is inside them, without removing the data queue entries.

Contained within the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9, is a SQL table function that allows me to view the contents of a data queue without the entries from being removed.

In this post I am not going to repeat descriptions of the other SQL data queue views, procedures, and table functions as all of that is described in detail here.

The new table function is DATA_QUEUE_ENTRIES, and is found in the QSYS2 library. Being a table function it has a number of parameters:

Tuesday, December 8, 2020

Changing how Run SQL Scripts displays null

ac change how null displayed and connection timeout time

I have now had a little bit of time to play with the new version of ACS, 1.1.8.6, and I have found a couple of things I thought were worth sharing with you. These are how null is displayed in the results and the connection time out.

The default null value has always been a hyphen ( - ), but as character fields/columns could contain just a hyphen how do I tell the difference between column that has just a hyphen and null in my results?

Monday, December 7, 2020

New version of ACS, 1.1.8.6

acs 1.1.8.6

IBM has launched a new version of their Access Client Solution tools on December 1. ACS is so much more than 5250 emulation, it also includes:

  • Data transfer to and from IBM i
  • IFS explorer tool
  • Navigator for i
  • Printer output control
  • Run SQL scripts
  • And a lot more!

It is offered for free, and can be downloaded from IBM's website at: ibm.biz/IBMi_ACS

Wednesday, December 2, 2020

New SQL table function lists all dependent objects

related_objects for pf and sql table dependent objects

This is one of my favorites in the latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9. The RELATED_OBJECTS table function lists the objects dependent upon the one given in its parameters.

I am not going to give the source code for all the objects I mention here. I will give you links to other posts where I describe how to create and use these different objects types.

I can get lists of dependent objects on a physical files using various CL commands, but this gives me a complete set of results in one place. And I am all for keeping things simple, KISS.

RELATED_OBJECTS has two mandatory parameters that need to be passed to it:

Tuesday, December 1, 2020

Formatting numbers in SQL

adding thousand separators in SQL numbers

I am sure I am not the only person when I get a number returned in my SQL results I have to look at it carefully to determine is that number millions, billions, trillions, or even bigger?

SELECT SUPPORTED_VALUE 
  FROM QSYS2.SQL_SIZING
 WHERE SIZING_ID = 18303


SUPPORTED_VALUE
---------------------
 18446744073709551600

For an explanation of what the SQL View SQL_SIZING shows see here.

Those of us who have been programming for any time in IBM i have formatted numbers in DDS files using edit codes and edit words in the source:

Wednesday, November 25, 2020

Writing to an IFS file with SQL

ifs_write ifs_write_utf8 ifs_write_binary to write content to an ifs file

This post continues yesterday's theme of doing things to files in the IFS with SQL. Then it was how to read an IFS file, today will be the opposite writing to an IFS file.

This was added in the latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.4 TR3. Prior to these TRs I had used C APIs embedded in a RPG program. Having played with this this is so much easier using these new SQL procedures.

As with the IFS read table function there are three different SQL procedures to write to an IFS file:

  1. IFS_WRITE:  Write plain text to the IFS file
  2. IFS_WRITE_UTF8:  Write UTF8 text
  3. IFS_WRITE_BINARY:  Write binary text

All of these procedure have the same parameters:

Tuesday, November 24, 2020

Reading a file in the IFS with SQL

read ifs file using new sql table functions ifs_read

One the latest additions to Db2 for i in the latest Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, was a Table function offering us the promise of reading a file in the IFS.

In the past I gave an example of how to read a file in the IFS in a RPG program using UNIX-type APIs, but the promise of this Table function makes this new approach look so much easier.

There are three new Table functions that basically do the same thing, which one to use depends upon which format you want the data returned in:

  1. IFS_READ:  Returns the data as plain text
  2. IFS_READ_UF8:  Returns the data in UTF8 format
  3. IFS_READ_BINARY:  Returns the data in a binary string

Wednesday, November 18, 2020

New RPG features for arrays

%list built in function and for-each operation code for arrays

The new Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, has given us a couple of new additions to array handling in the RPG language.

The first is a Built in Function, %LIST, that allows me to fill an array in one statement, and introduced me to a concept I had not heard of in the RPG language before, a temporary array.

The other is an operation code, FOR_EACH, that allows me to read an entire array, one element at a time in a loop operation without having to condition the loop with the number of elements in the array.

Let me start with %LIST Built in Function.

Tuesday, November 17, 2020

Range test added to RPG

%range bif added to rpg

The latest Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, have added something I have wanted for a long time added to the RPG programming language, the ability to test if a value is within a range. This has been achieved by the introduction of a new Built in Function, %RANGE, and a new operation code, IN.

The syntax is very similar to the Range test I use in SQL:

if <value or variable> in %range(<minimum value or variable> :
                                 <maximum value or variable>) ;

Let me get on with some simple examples. First with a date:

Friday, November 13, 2020

TR day! 7.4 TR3 and 7.3 TR9

ibm i 7.4 tr3 and 7.3 tr9 day

Today is an exciting day as the new Technology Refreshes, TR3 for IBM i 7.4 and TR9 for 7.3, are now available to download via PTF.

I wrote about what is in these when they were announced in October.

Links to the find the PTFs are:

I am now off to generate a support ticket to ask RZKH to load all of these PTFs. I can then start writing about all of the cool new stuff!

Wednesday, November 11, 2020

Entry plist in all free RPG

entry plist in totally free rpg

This post comes under the category of "I thought I had already written about this". I have written about calling programs with totally free RPG, and how to receive passed parameters to a RPG program with a main procedure, but I just forgot to write about how to receive parameters to a RPG program without a main procedure.

In fixed format RPG the entry parameter list would look like:

   CL0N01Factor1+++++++Opcode&ExtFactor2+++++++Result+
01 C     *entry        plist
02 C                   parm                    Parm

Now how do I do the same in free?

Wednesday, November 4, 2020

Getting "Run SQL scripts" to use the library list

change run sql scripts to use library list

Perhaps I am the only person who experienced this issue with my "Run SQL scripts", but I am going to share the solution I found.

I have to say "Run SQL scripts" is probably my favorite IBM i tool. I open it when I start my work day, and I use it all day to help me develop and test various SQL statements before I copy them into programs, procedures, or source members that I then create the objects from using the RUNSQLSTM command.

When the latest release of Access Client Solutions, ACS 1.1.8.5, was released I download the new install files, uninstalled the version of ACS I already had, and ran the install program to install it onto my PC. When it completed and I started to use "Run SQL scripts" again I found that I had to qualify the object, file, view, etc. name with the library. If I did not I always received the message telling me that the object was not found.

Wednesday, October 28, 2020

Getting the day of the week in a CL program using SQL

getting day of week in a cl program

The germ for this post came from a Facebook post I saw. The question was posed: Is it possible to retrieve the day of the week in a CL program? Multiple people suggested using a CEE API. Then came a reply that really interested me. This person said he had built a SQL View for this purpose.

It struck me as a creative approach to this scenario. SQL has a global variable CURRENT_DATE which contains, as the name suggests, the current date:

SELECT CURRENT_DATE
  FROM SYSIBM.SYSDUMMY1

00001
----------
2020-10-28

What kind of information would I want to know about today's date?

Wednesday, October 21, 2020

Using SQL to extract packed decimal from a flat file

sql interpret to extract packed decimal from strings

I have a two "flat" files that contain packed numeric fields. The first is a "header" that contains the file date, the number of records and the total of the amounts in the "detail" file. The "detail" file contains account information that I don't really care about. I need to validate the count from the "header" file to the data in the "detail" file.

When I look at the file using the Display Physical File Member command, DSPPFM, I can see the packed fields. They are where the funny characters are:

DSPPFM FLATHDR

*...+....1....+....2....+....3
2020-10-01??????Ãí_

DSPPFM FLATDTL

*...+....1....+....2....+....3....+....4....+....5
First     ??One  ?????¬*Testing
Second    ??Two  ???Ì°?*Testing

Wednesday, October 14, 2020

Which libraries did Jane create?

get the libraries owned by a specific user profile

I was asked how can I identify the libraries a person, in this example we will call her Jane, created? The person who asked the question was a self-confessed “newbie operator" who had been given some information by his employer's programmers, but doubted it was the most efficient way to get the data.

If I need to find out who created one library I would use the Display Object Description command, DSPOBJD.

DSPOBJD OBJ(MYLIB) OBJTYPE(*LIB) DETAIL(*FULL)

The following screen would be shown, and I would just look at the “Created by user" field:

Wednesday, October 7, 2020

Using multiple SQL Sequences to update different record types

sql sequence to update file with unique sequence number for each record

There is a file, ACCTFILE, with a unique key of two fields:

  1. SEQUENCE:  A unique sequence number
  2. ACCTYPE:  Account type code
01 A                                      UNIQUE
02 A          R ACCTFILER
03 A            SEQUENCE       6P 0
04 A            ACCTYPE        2A
05 A          K SEQUENCE
06 A          K ACCTYPE

The Sequence is incremented every time a record is written to the file, i.e. there is only one record with each Sequence number. The Sequence is now in the 800,000s, and the concern is what will happen when happen when 999,999 + 1 happens?

This is when they asked me for advice.

Tuesday, October 6, 2020

7.4 TR3 and 7.3 TR9 announced

new technology refreshes 7.4 tr3 and 7.3 tr9 announced

The autumnal announcements have been made for new Technology Refreshes for the currently supported versions of the IBM i operating system:

  • IBM i 7.4 TR3
  • IBM i 7.3 TR9

The TRs include the same updates for Db2 for i (SQL), RPG, and open source.

Saturday, October 3, 2020

New functionality added to this site

Have you noticed that there is a new option on the menu at the top of this page?

I have added a new page that contains a way to search this blog using a Google custom search engine.

Click on the link at the top of any page in this web site, and go search for the information you are looking for.

Happy searching!

Wednesday, September 30, 2020

Create or replace for SQL Tables

create or replace table on replace values

In previous posts I have given examples of using the CREATE OR REPLACE for all kinds of SQL objects: Views, Indexes, Functions, Triggers, Sequences, etc. One situation I have not mentioned is using this option when creating a Table. I know it sounds a bit scary to replace an existing Table that contains data. If I am going to do it I want to have the option to preserve the data or to delete it.

Creating a new table is as easy as:

01  CREATE TABLE MYLIB.TESTING_TABLE
02  FOR SYSTEM NAME "TESTTABLE"
03  (
04    FIRST_COLUMN FOR "FIRST" VARCHAR(10) NOT NULL,
05    IDENTITY_COLUMN FOR "ID_COL" BIGINT
06      GENERATED ALWAYS AS IDENTITY
07  ) ;

08  INSERT INTO MYLIB.TESTTABLE (FIRST_COLUMN)
09  VALUES('FIRST'),('SECOND'),('THIRD') ;

Wednesday, September 23, 2020

Retrieving commands' defaults, CLOB-ing it

using clob with qcdrcmdd api

Last week I wrote about how to use an API to retrieve a command's defaults. I encountered a problem when the string returned by the API would be blank. I did not know what was causing this. Fortunately two people added comments to the post explaining the issue: if the string returned is longer than the size of the result variable blank is returned. I want to thank both of them for letting me know what was causing this problem.

Therefore, if I increased the size of the subfield in the API's result data structure I see the results for those commands I did not see before.

This caused another problem, as I could no longer use the field in the DDS file I had defined before. The maximum size of an alphanumeric field is 32,766 bytes, which proved too small for some of the commands I tested with. My first thought was to use a (SQL) DDL table instead, but the maximum size of a character column is one byte smaller than for the DDS alphanumeric field, 32,765. As these XML strings can be extremely long I will need to insert these into a CLOB, Character Large Object, column as this can contain up to 2,147,483,647 bytes, way more than I could ever need.

My SQL DDL table looks like:

Wednesday, September 16, 2020

Retrieving commands' defaults

qcdrcmdd api to get command definition

I wanted to retrieve the defaults for a number of IBM commands, so I could compare them to those in another IBM i partition. I found an API, QCDRCMDD, would give me a XML string of the command's definition, that I could then write to a file. This file could then be transferred to the other partition and used when comparing the two sets of commands' information.

The output file, OUTFILE, would contain one field, for the XML string, but would need to big enough to contain the longest possible XML string. My guess is that a field of 10,000 characters would be long enough to cover most commands.

01  A          R OUTFILER
02  A            FIELD      10000A

Wednesday, September 9, 2020

Using Navigator for i for SQL index suggestions

using sql index advisor to create indexes

I have been playing with the new release of Access Client Solutions, ACS 1.1.8.5, and have been impressed with it. I have found Navigator for i a bit faster so thought it was time to revisit one of the coolest features included in it, the Db2 for i index advisor.

I have written about using the index advisor using the old Client Access Operation Navigator before. The ACS version of the index advisor is so much easier to use I wanted to encourage you to look at it.

When you access a table or file using SQL the Db2 engine determines the best access path to use to extract the data requested. If there is not a suitable existing access path it will build a temporary one. When it does, it makes an addition to the index advisor table with the suggested key, or if there is already a row it increments the "times used" counter. I can use the index advisor to see which indexes are suggested. If there is a suggested index that has been used many, many times then I will build it to make the SQL faster, as it no longer has to build the temporary access path.

Wednesday, September 2, 2020

Prestart jobs' information using SQL

sql to view prestart jobs

What are prestart jobs? They are batch jobs that run to allow programs on remote systems to communicate with the IBM i partition. They use prestart job entries that are found in the subsystem descriptions of the subsystems in which they run.

Previously the only way you could list the information about prestart jobs was to use the Display Subsystem Description command, DSPSBSD, one subsystem at a time, or the Display Active Prestart Job command, DSPACTPJ, one job at a time. The latest Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, introduced the following to Db2:

  • PRESTART_JOB_INFO View
  • PRESTART_JOB_STATISTICS Table function

Both of these are found in the QSYS2 library.

Thursday, August 27, 2020

New Db2 for i poster

Scott Forstie, Db2 for i Business Architect, has updated his Db2 for i poster to include everything from the latest Technology Refreshes, and made it available for download at ibm.biz/Db2foriPoster.

Thank you Scott for doing this.

In the future if that link stops working, as ones for previous posters have, you can download it from here.

Wednesday, August 26, 2020

Difference in Run SQL Scripts

acs run sql scripts content assist

I have had the latest version of Access Client Solutions, ACS, 1.1.8.5 for just over a week. I use "Run SQL Scripts" so much I leave it open all day. It is my favorite part of ACS, as I often start with a simple SQL statement, and then copy that to make it ever more complex until I have the final form of the statement I want to use. Something that is hard to do using the STRSQL interface.

There are several ways to start "Run SQL Scripts". The most common way is from the 5250 emulation display. I will find the option to start it in the "Actions" menu.


Tuesday, August 25, 2020

Wednesday, August 19, 2020

Using multiple data structures to contain the results of a Fetch

single row fetch into multiple data structures in rpg

The germ for this post came from a question from Bob. It is not the question that I wanted to share or the answer I came up with, but the solution he came up with. Bob was presented with an old program with a SQL Cursor definition statement that joined several files together. Within the Cursor definition the original programmer had listed all of the fields in the files, and in the Fetch statement he/she had listed all the fields from the files again.

The question he asked me was this possible to convert from a Cursor Fetching one row at a time to a multiple row Fetch.

In the following examples I am not going to use the files Bob used, but these test files:

Tuesday, August 18, 2020

IBM video: The Legacy and Future of IBM POWER with IBM POWER10

IBM reveals the next generation of IBM POWER10 processor

IBM press release here.

Interview with IBM Cognitive Systems General Manager Stephen Leonard here.

Monday, August 17, 2020

Time to give RPG a new name

help make rpg for i a reality

I was talking to some friends about totally free RPG and I soon got tired having to save "totally free RPG" all the time. Then it struck me this language needs a new name!

Do not worry I am proposing we drop the name "RPG", but the time is right to give it something to show that this is a new version of the programming language. When the AS/400 was launched, 32 years ago, RPGIII from the System/38 was renamed RPG/400. 25 years ago ILE/RPG was brought forth giving us a new paradigm to programming.

Five years ago "totally free RPG" was introduced. It is not ILE/RPG. There are no columns and I can start typing in the first column of the source member, and can continue the way to the last column too. It was something new and different from its antecedents. This is a new version of the programming language and, in my opinion, it deserves a new name.

Friday, August 14, 2020

New version of ACS: 1.1.8.5

acs version 1.1.8.5 download

A new version of IBM's Access Client Solutions, ACS, has been released. ACS includes:

  • 5250 emulator
  • Printer emulator
  • Navigator for i
  • Run SQL scripts
  • File transfers
  • And a lot more

And it is free to download from IBM's website.

If you are using a free IBM i service, like RZKH's PUB400.com, then this is what you should use as it gives you a lot more than the other 5250 emulators.

Wednesday, August 12, 2020

Using table function rather than view for object privilege information

object

One of the new arrivals with the latest rounds of Technology Refreshes was a table function to retrieve the authority for a particular object. "Wait!" I hear you regular readers say, "Wasn't there already a SQL View added to show that information?" And you are correct in IBM i 7.3 TR2 the OBJECT_PRIVILEGES View was released.

With a Table function I pass it a set of parameters and only the results for those values are returned. If I use a View then the entire View has to be searched for the rows matching the selection criteria. By using a Table function, rather than a View, I can get the information I want quicker. I will demonstrate this in the examples below.

The OBJECT_PRIVILEGES Table function and View have the same columns. The only annoying difference is the name of the authorized user profile in the View is AUTHORIZATION_NAME, while in the Table function it is AUTHORIZATION_USER.

In these example I am only interested in determining if user profile is authorized to use a file, TESTFILE. The profiles authority is held in the column OBJECT_AUTHORITY. Therefore, if I want to see who is authorized to use the file TESTFILE in the library MYLIB I would need to pass the Table function the following:

Tuesday, August 11, 2020

Removing deleted records faster than RGZPFM

cpyf quicker than rgzpfm

The subject of this post is not new, but I thought I would share this as this is the quickest way I know to get rid of millions of deleted records taking up space in your files. The last time I used this method was with a file that contained 1 million "active" and 11 million deleted records. The application owner of this file had a fixed amount of time to remove the deleted records in their weekly maintenance "window". Having performed tests using RGZPFM she found that it took longer than the allowed, and came to me for ideas.

RGZPFM FILE(BIGFILE)

The part of this process that many people forget is all the access paths are reorganized too. In this case there were a plethora of logical files built over this file, I forget exactly how many but too many for my liking.

What was my suggested alternative?

Thursday, August 6, 2020

Viewing IFS object's authority using SQL

using sql to view authority of files and folders in ifs

Included with the latest Technology Refreshes is a new table function which allows me to view objects' privileges of the folders and files in the IFS. Object privileges/authorities are something that I am asked for during audits, therefore, I was really interested to learn what information I can get from it.

The only gotcha is as a table function IFS_OBJECT_PRIVILEGES returns the results for one set of objects, rather give me the ability to get information for all objects as I would using a view.

As I have done in many of my previous examples I am not going to show you all the columns returned by this table function, just the ones I find interesting. There is a link at the bottom of this post to IBM's documentation for this table function which includes a full list and description for all of the columns.

The columns I am going to work with are:

Wednesday, August 5, 2020

SELECT with EXCEPT clause to find differences between records in two files

using except clause to join 2 select statements together to see differences

I saw this mentioned in a Facebook discussion where someone said he was using what I will describe below to determine the difference between the records in two files. I had not heard of this method so I wanted to try it out for myself and determine whether it is something I could use in the future. Two SQL Select statements joined with an EXCEPT clause. But before I show examples of that I need data.

I am going to:

  • Create a file
  • Add data to it
  • "Clone" the file to make an exact copying, including the data
  • Change the data in the "clone"
  • Compare the two files

In these examples I am just going to use a file with one field. The same principles work with one field as it would with a hundred fields. I decided to K.I.S.S. and just have one field in my file. The layout for the file is:

Thursday, July 30, 2020

Viewing HTTP server information using SQL

view data about apache http server on ibm i

I want to give credit to IBM's Db2 for i team for continually adding SQL views, table functions, etc. to information that was not easy to get using APIs or other tools. Another example of their excellent work, introduced with the latest Technology Refreshes, is the view HTTP_SERVER_INFO. Previously this information was only available using the Web Administration for i GUI.

This view, which is located in the library QSYS2, displays information about the Apache server in the HTTP server for IBM i, and will only display data about the enabled and active functions.

As usual with my examples of Db2 for i views and table functions I am not going to list all of the available columns, just the ones I found interesting:

Wednesday, July 29, 2020

Removing apostrophe in a field using SQL

replace 1 character with another using rpg and sql

There are occasions when interfacing data to non-IBM i database where an apostrophe ( ' ) in a field can cause a conversion error. In this post I am going to give some simple examples of how apostrophes can be converted to something else using SQL and RPG.

Let me start with the DDS file I will be using in these examples. The file is called TESTFILE and contains one field:

A          R TESTFILER
A            COMPNAME      35A

The field contains the following three records:

Saturday, July 25, 2020

It's been IBM i for longer than AS/400

In a post on LinkedIn yesterday Steve Will, chief architect of the IBM i, shared an interesting milestone in the life of the this operating system.

This operating system had been known as IBM i for a long as it was known as AS/400.

Wednesday, July 22, 2020

Subtracting days from a "date" when it is a number in a file

substract 35 days from a date when it is a packed numeric field

The germ for this post came from a question:

How can I subtract 35 days from a date in my files using SQL? The date is a packed number

Most of us who deal with older databases find that the "dates" in the files are not true dates fields, they are numbers masquerading as dates. I have, in a previous post, shown how to change a numeric representation of a date to a date with SQL, this is taking it a step further by converting the date back to a number and updating the file with the new value.

In these examples I will be using the numeric representation of the date in two formats:

Tuesday, July 21, 2020

How to view pending database commits using SQL

finding uncommited transactions using DB_TRANSACTION_INFO view

Another addition to Db2 for i in the latest Technology Refresh, IBM i TR2 and 7.3 TR8, was a view called DB_TRANSACTION_INFO. This view lists what the IBM documentation describes as: "returns one row for each commitment definition".

What does that mean? Having played with this View for a while I have found that it just lists all uncommitted database transactions to a file or table that is using commitment control. Database operations to files and tables that do not use commitment control do not appear in this View.

There are far more columns in this View than I am going to use in my examples, so I am going to say to you if you want to know what all of the columns are click on the link to IBM's documentation at the bottom of this post.

Thursday, July 16, 2020

Viewing all of the subsystems autostart jobs in one place

view subsystem autostart jobs using sql

If I needed to see which autostart jobs were in my IBM i partitions I would either have to use the Display Subsystem Description command, DSPSBSD, to view the autostart jobs for each subsystem or use an API. Fortunately the latest Technology Refreshes include a Db2 for i View that gives me the information I want.

Before I give examples of using the new View I want to show how to get to the same information using the DSPSBSD command so that you can compare the data from the two.

If I want to see what autostart jobs there are in the QCTL subsystem I would use the following command:

DSPSBSD SBSD(QCTL)

When the Enter key is pressed the following menu is displayed:

Wednesday, July 15, 2020

Listing SQL Indexes' keys

using sysindexes and syskeys to make a list of index keys

Most of the time I do not care what the keys are for any SQL Indexes I have built over DDL tables or DDS tables. When I want to extract data from a table or file using SQL I build the statement using the table or physical file, and Db2 of i is smart enough to find the best access paths (logical file or SQL Index) for the statement.

I recently encountered a company where their programmers had been building Indexes. Building Indexes is not a problem, but each programmer had been building their own Indexes without consulting with the other members of the team. The team leader was concerned that they now had many indexes that had the same keys. He asked me if I could help him identify the following:

  1. Indexes built over which table or file
  2. The keys of those Indexes

Wednesday, July 8, 2020

Make second line appear and disappear on a subfile

subfile that expands to a 2nd line and contracts

I was asked how to do this by someone who said that no-one in his department knew how to: have a second record appear in a subfile when a function key is pressed, and disappear when the function key was pressed again.

Fortunately this is a simple thing to do with just three keywords in the display file's DDS:

  • SFLDROP
  • SFLFOLD
  • SFLMODE

And there is not much we have to do in the RPG program that uses the subfile.

I have deliberately stripped down the display file and RPG code to what I consider a minimum to, in my opinion, makes it easier to understand.

Let me jump right in and show you the display file I will be using in this example: