DB2 - L

 View Only
  • 1.  Inserting and not listing all the columns

    Posted Nov 09, 2021 08:59 AM

    Folks,

     

    Something to perhaps be aware of.  We've just noticed some new-ish (not worked out exactly when it changed TBH) behaviour involving programs that issue a static Insert statement where the statement doesn't list any of the columns, and the table has been altered to add more columns.  Seems upon rebind it now returns -117 as opposed to working fine.

     

    So a table with three cols being inserted into should say INSERT INTO MYTAB (COL1, COL2, COL3) VALUES (:hv1, :hv2, :hv3).  But we occasionally have code that says INSERT INTO MYTAB VALUES (:hv1, :hv2, :hv3).  And until recently this was fine, if a little odd.  Problem has arisen while we're doing some UTS conversions.  Seems some old packages with inserts coded this way previously had their tables altered to add some cols but they weren't rebound at the time.  Fine; if they don't need 'em who cares?  Sometime later those same objects were converted to UTS and the packages rebound, as the UTS alters issued invalidate the packages.  And guess what?  Some of those rebinds failed -117.

     

    My gut feeling is that IBM have shut the door on this 'undesirable' behaviour.  Which is annoying if they have because the SQL as it stands is fine.  The SQL Reference says an omitted column list is an implicit specification of all columns and that, if omitted, the data values provided are given to the table columns in left-to-right order.  All good; so dish out the values you have, and let the columns you don't have values for default, assuming they can.  Which given the issue is for tables that have been altered to add columns (which, ergo, must have a default value)... there's no real problem other than it looking a bit odd.

     

    Anyway, you might want to try out inserting into a table with no columns named on the insert and only providing values for some of the left-most columns.  Gives the same error with dynamic SQL eg. SPUFI.  Oh, and this was V12R1M502; not sure what RSU.

     

    Cheers,

     

     

    Raymond

     

    Raymond Bell
    Db2 Database Administrator, Mainframe Services, Core Solutions, Technology

    I use he/him pronouns

    A picture containing drawing  Description automatically generated
    raymond.bell@natwest.com
    +44 (0) 7894 608 214 |  natwestgroup.com
    250 Bishopsgate | London | EC2M 4AA

    The information classification of this email is Confidential unless otherwise stated.

     


    National Westminster Bank plc. Registered in England No. 929027. Registered Office: 250 Bishopsgate, London EC2M 4AA. National Westminster Bank plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.
    The Royal Bank of Scotland plc. Registered in Scotland No. 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.
    The Royal Bank of Scotland plc and National Westminster Bank plc are authorised to act as agent for each other.
    The Royal Bank of Scotland plc and National Westminster Bank plc are UK chartered banks and are not chartered or licensed as banks by the United States or any individual state.
    This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, National Westminster Bank plc or any affiliated entity ("NatWest" or "us") does not accept responsibility for changes made to this message after it was sent. NatWest may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by NatWest in this regard and the recipient should carry out such virus and other checks as it considers appropriate. 


  • 2.  RE: Inserting and not listing all the columns

    Posted Nov 10, 2021 06:55 AM
    The SQL Reference says an omitted column list is an implicit specification of all columns and that, if omitted, the data values provided are given to the table columns in left-to-right order. 

    I can see where that makes sense...if you were to specify INSERT INTO MYTAB (COL1, COL2, COL3, COL4) explicitly, with VALUES (:hv1, :hv2, :hv3) it would likely fail.

    It sounds like what you said, that IBM was allowing this practice and then decided to shut it down at some point. The question is "when", if your packages hadn't been rebound for some time.

    You might want to open a ticket and ask IBM whether there's anything you can do to get operating again without changing your code.





    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 3.  RE: Inserting and not listing all the columns

    Posted Nov 10, 2021 07:19 AM

    Hi Mark,

     

    PMR already open.  Waiting to see what the response is.  We had to back out the UTS conversion.  There are some very not-happy campers here as a result.

     

    Cheers,

     

     

    Raymond

     






  • 4.  RE: Inserting and not listing all the columns

    Posted Nov 10, 2021 11:07 AM
    Let us know how it goes...kinda interested. This sounds like something that developers at a lot of shops might do. :)

    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 5.  RE: Inserting and not listing all the columns

    Posted Nov 10, 2021 11:57 AM
    Won't APPLCOMPAT help here? Isn't that what it's for??

    Pretty sure this must be a fairly recent change otherwise there'd have been more shouting !

    Phil G

    Sent from my iPad





  • 6.  RE: Inserting and not listing all the columns

    Posted Nov 10, 2021 06:16 PM
    Depends when the last REBIND was...could have been a while ago. He could try a back-level APPLCOMPAT.

    Could have been introduced with UTS, too. *shrug*


    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 7.  RE: Inserting and not listing all the columns

    Posted Nov 11, 2021 03:53 AM

    Gents, APPLCOMPAT isn't an issue here – a good chunk of our programs still say V10R1 for some reason.  And it may well have been introduced with UTS but I doubt it; it's an issue for programs that insert this way into non-UTSs as well.

     

    Something I noticed yesterday.  My Tech Lead ran around and got a list of programs potentially impacted.  And one of them is... ADBTEP2.  Yup, one of IBM/Rocket's own products is breaking their own rule.  Do as I say, not as I do, eh?

     

    If I were a betting man I'd put a pound on it indeed being a recent change, probably some recent PTF wots been applied and has started to enforce this apparent rule, implied by the fact 'no cols specified' is the same as 'all cols specified', for which either way you obviously must provide a value for the insert.

     

    Will keep you posted.

     

     






  • 8.  RE: Inserting and not listing all the columns

    Posted Nov 11, 2021 04:18 AM
    Given the history (and longevity) of this usage, I'd suggest this is an incompatible change. I'm surprised more folks haven't been bitten!

    Phil G

    Sent from my iPad





  • 9.  RE: Inserting and not listing all the columns

    Posted Nov 11, 2021 06:39 AM

    Hi all,

    just my €0,01 on the subject.

    I don't believe this is a recent change.

    Db2 has always put dependent package in status VALID='A' whenever a column is added to a table.

    VALID='A' means it is *still* executable but is better to rebind. But if you have an insert statement like this you are not able to rebind the package since you get -117 errors.

     

    When a conversion to UTS occurs, the package goes to VALID='N' state, so not executable *until* you rebind. But you cannot rebind...

     

    In our environment we chase VALID='A' packages and tell developers to correct the SQL including the column list before it is too late.

     

    Mauro Moschelli


    There are 10 types of people: those who understand binary and those who don't