IBM i (iSeries) View Point Script

Question

I am trying to take what is currently stored in ViewPoint as a Script that is run and convert it over to SQL and scheduled Tasks. My issue is well simply put I don't know the scripting language used for I Series. Can anyone tell me what the resulting value will be for &STARTS &STARTS2 and &STARTS3

CHGVAR VAR(&OFFSET) VALUE(0)
CHGVAR VAR(&STARTS) VALUE(CYYMMDD(DATE(MONTH(Current Date- (&OFFSET + 1) Month)||'/22/'||YEAR(Current Date-(&OFFSET+1) month))))
CHGVAR VAR(&STARTS2) VALUE(CYYMMDD(DATE(MONTH(Current Date- (&OFFSET + 2) Month)||'/22/'||YEAR(Current Date-(&OFFSET+2) month))))
CHGVAR VAR(&STARTS3) VALUE(CYYMMDD(DATE(MONTH(Current Date- (&OFFSET + 3) Month)||'/22/'||YEAR(Current Date-(&OFFSET+3) month))))

Answer 1

One of the relatively nifty things about the iSeries is that it lets you do 'natural language' date math: current_date - 2 months is exactly what it sounds like, a date that is precisely two months before today.

So, assuming that it actually works as written, what this script is doing is:

• set the variable &OFFSET to 0. (the CHGVAR command is the iSeries's verbose way of assigning a value: in most languages you would write &OFFSET = 0 instead, and even with the iSeries you could shorten it to simply CHGVAR &OFFSET 0.)
• set the variable &STARTS to the 22nd of last month, where the date is in the CYYMMDD format. (It does this by constructing the date based on "use the month that is one month before today, 22 as the day, and the year of the month that is one month before today", and then converting that to CYYMMDD format.
• sets the variable &STARTS2 to the 22nd of 2 months ago
• sets the variable &STARTS3 to the 22nd of 3 months ago

If you were to tweak the value of &OFFSET (by changing its VALUE(0) assignment), your calculated dates would be that much further in the past: setting it to VALUE(4) would get you dates of 5, 6, and 7 months ago.

In the CYYMMDD format, by the way, the 'C' is a counter of centuries since 1900; essentially, the date is an integer of the form YYYYMMDD - 19000000. So, 1/1/1999 = 19990101 in YYYYMMDD format, or 0990101 in CYYMMDD format; 12/31/2011 = 20111231 in YYYYMMDD format, or 1111231 in CYYMMDD format.

So if you ran this today, 8/19/2015, you should get results of:

• &STARTS = 1150722
• &STARTS2 = 1150622
• &STARTS3 = 1150522

By the way, the language your script is written in is called "CL" (short for "Command Language"), and the official language reference is at The IBM Knowledge Center.

Answer 2

Can anyone tell me what the resulting value will be for &STARTS &STARTS2 and &STARTS3

TL;DR: The resulting values will be undefined since only the first CHGVAR command might compile. The others have many problems. As pseudocode, the statements are okay, but they will expand into a large body of code when expressed as actual CL statements.

Details:

One underlying problem is thinking of CL as a "scripting language". While it certainly can be, and commonly is, used for "scripting", it is the system's "Control Language". As far as "scripting" goes, it is just about as easy for a decent C programmer to replace CL with C as the "scripting" language. Similarly, I've seen RPG developers who do much/most of their "scripting" in RPG. Similarly for COBOL.

CL is also the system's "command language". In currently supported versions of the OS, you can expect to find nearly 2000 commands. Numerous system functions can only be done by command, or by invoking or calling a program that executes the command. Every native language can execute most commands interactively by passing the command string to one of the system's command processing APIs. Even CL, which is a compiled language, can execute commands interactively the same way other languages do.

Still, CL is compiled. It also can fully participate as an ILE language, most especially beginning at the V5R4 version of the OS, but technically since ILE was first introduced in the AS/400 line.

As an ILE participant, CL has full access to the same features other native languages have. For example, CL can use the C run-time library of functions. So, in addition to native CL command capabilities, it can essentially do anything ILE RPG can do. You can even compile CL *MODULEs and bind them together to create service programs (think Windows .DLLs or UNIX function libraries).

But one thing CL does not have is a SQL pre-processor.

While there are a couple commands for executing interactive SQL statements, there is no facility for doing something like VALUES INTO or FETCHing from a cursor or getting results from a SQL function such as DATE().

Well. technically, I suppose we can say that it can be done because it is "ILE CL" after all. Because of that, CL can call the various SQL CLI APIs (think ODBC). And because of that, you can program CL procedures with calls to SQLAllocEnv(), SQLAllocConnect(), SQLConnect(), SQLPrepare() and all the other functions that might be needed.

But what you can't do in CL is anything like what you'd like to do in the VALUE() parameters of your last three CHGVAR commands. You could certainly code CL procedures to perform the date calculations and manipulations. Like others early on, I wrote various detailed date-math functions in CL (as well as other languages) before easy access was available to various date/time APIs. Then, with the APIs, most were replaced with more elegant ones.

CL is arguably the most complex language on the system. But it doesn't do SQL. At least not like C, RPG or COBOL can. CL is intended as a "control" language, not a database nor an application language.

There is an alternative though. There is a native "scripting" language that can run SQL -- REXX. It would be far easier to use REXX to do this than CL. Your VALUE() clauses could almost be lifted and pasted directly into an appropriate REXX procedure. You could have a CL wrapper to invoke the procedure for each of your complex VALUE() parameters and to pull the result back from REXX. Or you could have REXX do all three at once.

But if you want CL to do it all, there is a lot of coding yet to be done.