Wednesday, August 28, 2019

Parms built in function added to CL

using bif parms to find number of parameters passed

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:

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.