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

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?

Using SQL to extract packed decimal from a flat file

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

Which libraries did Jane create?

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:

Using multiple SQL Sequences to update different record types

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.

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.

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!

Create or replace for SQL Tables

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') ;

Retrieving commands' defaults, CLOB-ing it

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:

Retrieving commands' defaults

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

Using Navigator for i for SQL index suggestions

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.

Prestart jobs' information using SQL

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.

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.