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!