Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    30

    Unanswered: DB2 - CICS : Will the Plans get Invalidated?

    Hi,

    We have a DB2 7.1 on z/OS and a CICS application running on mainframe system. We are planning to add a timestamp column to existing tables, which are used by some transactions developed in CICS.
    By adding a timestamp filed, will the plans get invalidated? and do we need to rebind the transactions which use these modified tables?

    Thanks
    Santhi

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, but hopefully you don't have "select *" in your code on that table. If the select * is on a view that has not changed, that is OK.

    If you change your code to access the new column, then obviously the associated DBRM will be need a BIND REPLACE.

    The alter table to add a column will not actually create physical space on each row for the existing data, so for best performance it is best to do a reorg at the next convenient oppurtunity if you want to update existing rows.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2003
    Posts
    30
    Hi,
    Thanks for your immediate reply.

    We don't have any select * statements. Even if they are there is a view created on each of these tables. All the transactions access only views, they do not refer to physical tables.

    The timestamp is added for audit reasons and we have triggers to update this timestamp. The transactions have nothing to do with this new field. Also we dont want to update this new column for all the existing data right now. When the record is touched the trigger updates the timestamp.

    The actual reason for my qn is the CICS package is maintained by 3rd party. We dont want any impact/effort on this package because of this new field addition.

    So can I assume that they do not have to do any thing for this new field addition and triggers?

    I appreciate your help.
    Thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It should have no impact on the application software.

    In DB2, even if a plan dependent object is changed or dropped (and the plan gets marked as invalid), the first execution of an invalid plan causes an automatic rebind (which should succeed if the dependent object is recreated). If the automatic rebind fails for any reason, then the plan is marked as inoperative and an explicit rebind must be performed by the programmer/DBA.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2003
    Posts
    30
    Hi,

    Sorry to trouble you again.
    When I was referring to DB2 manuals I got this info.

    Invalidation of plans and packages: When a table is altered, all the plans and packages that refer to the table are invalidated if any one of the following conditions is true:

    The AUDIT attribute of the table is changed.

    A DATE, TIME, or TIMESTAMP column is added and its default value for added rows is CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP respectively.

    I justed wanted to check whether this is true as per the documentation.
    Thanks
    Santhi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •