• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Gotcha Lurking in Datalink File Manager for DB2/400

    January 17, 2007 Hey, Ted

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Raz-Lee Security

    Start your Road to Zero Trust!

    Firewall Network security, controlling Exit Points, Open DB’s and SSH. Rule Wizards and graphical BI.

    Request Demo

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    BCD:  Try WebSmart - the easiest and most complete iSeries Web development tool
    COMMON:  Join us at the 2007 conference, April 29 – May 3, in Anaheim, California
    New Generation Software:  Leading provider of iSeries BI and financial management software

    IBM to Open Eight SOA Centers Worldwide Big Blue Readies Revamped Storage for the System i

    Leave a Reply Cancel reply

Volume 7, Number 2 -- January 17, 2007
THIS ISSUE SPONSORED BY:

WorksRight Software
SEQUEL
Patrick Townsend & Associates

Table of Contents

  • Using APIs to Send Impromptu Messages, Take Two
  • Gotcha Lurking in Datalink File Manager for DB2/400
  • Admin Alert: Ending Subsystems Properly

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • What You Will Find In IBM i 7.6 TR1 and IBM i 7.5 TR7
  • Three Things For IBM i Shops To Consider About DevSecOps
  • Big Blue Converges IBM i RPG And System Z COBOL Code Assistants Into “Project Bob”
  • As I See It: Retirement Challenges
  • IBM i PTF Guide, Volume 27, Number 41
  • Stacking Up Power11 Entry Server Performance To Older Iron
  • Big Blue Boosts IBM i Support In Instana, Adds Tracing
  • It Is Time To Tell Us What You Are Thinking And Doing
  • IBM i PTF Guide, Volume 27, Number 40
  • The GenAI Boom Is Only Slightly Louder Than The Dot Com Boom

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle