Parms built in function added to CL

With every new release I go through all the "What's new for this release" section of IBM's KnowledgeCenter. For IBM i 7.4 and the CL programming language there is only one addition this release: %PARMS built in function. It appears to be identical to the %PARMS built in function in RPG. It is the way to determine how many parameters have been passed to a program or procedure, and from that stop the program from erroring if too few parameters are passed.

In the past I wrote about coping with to few parameters being passed to a CL program, but the new BiF is a lot neater and easier for someone else to understand.

As I said above %PARMS is available in RPG:

Executing CL commands using 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.

More about SQL Sequences

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 ;

Using a SQL Sequence to renumber a column

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.