Gotcha Lurking in Datalink File Manager for DB2/400

I’ve grown quite fond of the convenience of the DB2 SQL support in QShell, and especially the ease with which it can be invoked within a CL program, by simply calling the underlying Datalink File Manager DB2 program, QZDFMDB2. Having said that, I must also admit to getting burned by a “gotcha” lurking in this otherwise handy tool.

Although I’ve never experienced a problem when providing QZDFMDB2 with an SQL statement in the form of a constant, I did walk straight into a real head-scratcher recently when building the parameter string for this program from variable data. I kept getting an “Invalid token” error, and the “token” was the first few characters of a variable defined in my CL immediately following the definition of the parameter being passed to QZDFMDB2. Here’s a snippet of my source to illustrate:

DCL VAR(&CMD) TYPE(*CHAR) LEN(512)
DCL VAR(&TEXT) TYPE(*CHAR) LEN(50)
...
 CHGVAR VAR(&CMD) VALUE('create table qtemp.ampud as +
              (select * from am2000.ampud where uduser +
              = ' *CAT '''' *CAT &MODELUSER *TCAT '''' +
              *CAT ') with data')
 CALL PGM(QZDFMDB2) PARM(&CMD)

Now, this call worked just fine. No doubt feeling cocky at this point, I went on to execute:

CHGVAR VAR(&CMD) VALUE('update qtemp.ampud set +
             uduser = ' *CAT '''' *CAT &NEWUSER *TCAT +
             '''' *CAT ', udtext = ' *CAT '''' *CAT +
             &TEXT *TCAT '''')
CALL PGM(QZDFMDB2) PARM(&CMD)

Well, the walls came a-crashing at that point, with the aforementioned token error. I tried increasing the length of my &CMD parameter, to 2000. No luck. I tried 5000. Nope. I tried moving the Declare of &CMD to the end of the variables. Zippo. At that point there must have been a divine intervention, for it occurred to me to add a semicolon terminator to my SQL statement. Voilà! Here’s the simple change:

CHGVAR VAR(&CMD) VALUE('update qtemp.ampud set +
             uduser = ' *CAT '''' *CAT &NEWUSER *TCAT +
             '''' *CAT ', udtext = ' *CAT '''' *CAT +
             &TEXT *TCAT ''';')

Apparently the SQL processor just keeps sifting through all those trailing blanks in my parameter, and trudges right into whatever variable gets in its way. I haven’t tried setting the length to 65,535 or 32,767 or whatever IBM may have set the boundaries to, but I think I’ll just always include a terminator to be safe. Of course, that’s when I remember to! 🙁

By the way, as for the first statement executing without error, my guess is that “with data” satisfied the SQL processor as the last “tokens” expected in that syntax, whereas my update statement could have continued–after 4000 spaces! Yeah, I like a little white space in the middle of my code.

–Jim Rothwell, NBC Universal

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot