Wednesday, August 21, 2019

Executing CL commands using RUNSQLSTM

adding cl commands to member used by runsqlstm

The Run SQL Statements command, RUNSQLSTM, runs all of the SQL statements that are in a particular source member. I use the command a lot as I put all of my SQL statements to create Tables, Views, Indexes, and the statements to Alter them into source members, and I update the member whenever I make a change to the object. I also put miscellaneous groups of SQL statements into source members, that I can then execute whenever I want.

I use RUNSQLSTM so much I have a PDM option defined so I don't have to type in the command and all the parameters I care about.

Opt  Command
 RS  RUNSQLSTM SRCFILE(&L/&F) SRCMBR(&N) COMMIT(*NONE) ERRLVL(20)

In the past if I ever wanted to run a CL command in one of these member I would use SQL's QCMDEXC procedure.

Wednesday, August 14, 2019

More about SQL Sequences

sequences view, select next value, select previous value

Last week I wrote a post introducing what SQL Sequences are. Having had a chance to "play" with them some more I wanted to write about what I have discovered.

Before I go into any examples I need to have some Sequences to "play" with:

CREATE SEQUENCE MYLIB.BY_ONE
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE ;

CREATE SEQUENCE MYLIB.BY_TEN
START WITH 10
INCREMENT BY 10
NO MAXVALUE
NO CYCLE ;

CREATE SEQUENCE MYLIB.BY_100
START WITH 100
INCREMENT BY 100
NO MAXVALUE
NO CYCLE ;

CREATE SEQUENCE MYLIB.BY_THREE
START WITH 30
INCREMENT BY 3
NO MAXVALUE
NO CYCLE ;

Wednesday, August 7, 2019

Using a SQL Sequence to renumber a column

sql sequence to provide a sequentail number to a sql statement

The title sounds a bit strange, but so is the subject of this post. There is a thing in Db2 for i that I can create called a Sequence. A Sequence uses the rules I give it to return to me a sequential value I can use to update column in a table.

I am sure it will become a bit clearer when I give my example.

I have ten students who have completed taken a course. At the end of the course the students take a test. The results of this test are contained in a SQL table I created, along with their name.

Wednesday, July 31, 2019

Extracting the job's name from the Job Name

extracting name part of job name

I was asked was there an easy way to extract the name from the Job Name column in the Db2 for i table function ACTIVE_JOB_INFO.

The IBM i Job Name consists of three parts:

  1. Number
  2. User profile
  3. Name given to the job when it was started

For example:

741232/SIMONH/QPADEV0001

The issue when extracting the name from the Job Name is it does not always start in the same position. The number is always six long, but the user profile can be any length from one to ten. This means that the name could start anywhere from the tenth to the nineteenth position in the Job Name.

Saturday, July 27, 2019

Thank you OCEAN UG

I wanted to take this opportunity to publicly thank the the organizers of OCEAN User Group's TechCon19 for an excellent event.

A "thank you" also goes to all the presenters who were at the event.

And another "thank you" to those who attended my presentation. I hope you found something interesting from it, that you will be use.

Wednesday, July 24, 2019

Using SQL to determine if subsystem is started

check if subsystem active and count of jobs in it

The question came in two parts:

  1. Is it possible to know whether a subsystem has been started?
  2. If it is started how can I determine the number of jobs running in it?

To answer these questions I decided to use a Db2 for i table function I have written about before ACTIVE_JOB_INFO.

One of the parameters that can be used with ACTIVE_JOB_INFO is one that narrows the results to just all active subsystems. If I wanted to know if QINTER subsystem was active I could use the following SQL statement in my favorite SQL client:

Saturday, July 20, 2019

Ocean’s TechCon 2019 in 6 days

In six days I will be giving my presentation SQL at Ocean UG’s TechCon 2019. I have my presentation finished, and I am ready to present it on Friday (July 26) at 1:00 PM in room 201. If you are attending this conference I hope you will consider attending my presentation.

If you are in So Cal there is still time to register to attend the best User Group conference in the West.

I hope to see many of you there!

Wednesday, July 17, 2019

Easiest way to retrieve the program's name in a CL program

this is the easiest way to get the cl program name

It has always been a bit of a pain to get the name of the CL current program using the Receive Message command, RCVMSG. Therefore, I was excited when I received an example program showing a much simpler way of getting the same information by using a Machine Interface program.

The program I am going to show needs to be created as a CLLE member, and compiled using the Create Bound CL program , CRTBNDCL. As the call procedure command, CALLPRC, is not permitted in CLP source members.

My CLLE program will be calling the MI program _MATPGMNM, it is important to notice that the program name starts with an underscore ( _ ). I cannot use a CALL command, I must use the CALLPRC command instead.

My first example is the simplest program I could write to call this MI program, and have the current program name and library returned.

Wednesday, July 10, 2019

Which users are authorized to these files and tables?

systabaut, system table authority, sql view

This post started as one idea, and the more I played it morphed into something else.

The starting point was the SYSTABAUTH view. This view displays the authority of all DDS PF and LF files, SQL DDL tables, views and indexes, in all libraries, except QTEMP. It contains the same information that is shown when I use the Display Object Authority command, DSPOBJAUT. As it is a view I can retrieve the results from SQL statements and manipulate it any way I desire.

In these examples I will be using a few of the view's columns:

  1. GRANTEE:  the user profile that has been authorized to the object
  2. SYSTEM_TABLE_SCHEMA:  the system name of the library that contains the object
  3. SYSTEM_TABLE_NAME:  the system name of the object
  4. PRIVILEGE_TYPE:  the various authorities granted to the user profile to this object

There are seven different types of privilege that can be given to an object: