Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Jun 26
    2003

    using Cobol and Identity columns.

    William Proctor
    Does anyone have an example of using db2 idenity columns GENERATED ALWAYS AS
    IDENTITY in a cobol program. I either get errors during the insert or on
    the bind depending on how I code the application. This is our first attempt
    at this and I'm not finding very good documentation on it.





    Bill Proctor

    Database Administrator (Adabas/DB2)

    Texas Guaranteed Student Loan Corp.

    Austin, Texas

    Phone: 512-219-4847





    Phil Grainger
    [BMC Software]
    Bill

    They are just normal numeric columns as far as COBOL is concerned. On the insert DON'T specify a value. Also, consider GENERATED BY DEFAULT instead of GENERATED ALWAYS - this will cause less problems later


    Phil Grainger
    Computer Associates
    Product Manager, DB2
    Tel: +44 (0)161 928 9334
    Fax: +44 (0)161 941 3775
    Mobile: +44 (0)7970 125 752
    [login to unmask email]

    -----Original Message-----
    From: Proctor, William [mailto:[login to unmask email]
    Sent: Thursday, June 26, 2003 5:00 PM
    To: [login to unmask email]
    Subject: [DB2-L] using Cobol and Identity columns.



    Does anyone have an example of using db2 idenity columns GENERATED ALWAYS AS IDENTITY in a cobol program. I either get errors during the insert or on the bind depending on how I code the application. This is our first attempt at this and I'm not finding very good documentation on it.





    Bill Proctor

    Database Administrator (Adabas/DB2)

    Texas Guaranteed Student Loan Corp.

    Austin, Texas

    Phone: 512-219-4847





    HEPP SHERY C
    Phil- I'm curious about your statement that generated always identity columns cause problems. Would you care to elaborate on this? I have an app that uses both types of identity columns and in my experiences we've had more problems with the generated by default rather than the generated always. Just wondering what gotchas may be lurking out there in the future.

    Thanks- Regards, Shery Hepp

    -----Original Message-----
    From: Grainger, Phil [mailto:[login to unmask email]
    Sent: Thursday, June 26, 2003 9:11 AM
    To: [login to unmask email]
    Subject: Re: using Cobol and Identity columns.


    Bill

    They are just normal numeric columns as far as COBOL is concerned. On the insert DON'T specify a value. Also, consider GENERATED BY DEFAULT instead of GENERATED ALWAYS - this will cause less problems later


    Phil Grainger
    Computer Associates
    Product Manager, DB2
    Tel: +44 (0)161 928 9334
    Fax: +44 (0)161 941 3775
    Mobile: +44 (0)7970 125 752
    [login to unmask email]

    -----Original Message-----
    From: Proctor, William [mailto:[login to unmask email]
    Sent: Thursday, June 26, 2003 5:00 PM
    To: [login to unmask email]
    Subject: [DB2-L] using Cobol and Identity columns.



    Does anyone have an example of using db2 idenity columns GENERATED ALWAYS AS IDENTITY in a cobol program. I either get errors during the insert or on the bind depending on how I code the application. This is our first attempt at this and I'm not finding very good documentation on it.





    Bill Proctor

    Database Administrator (Adabas/DB2)

    Texas Guaranteed Student Loan Corp.

    Austin, Texas

    Phone: 512-219-4847







    Antonis Galaios
    [EFG Eurobank]
    Hi Bill

    We recently upgrade our DB2 from V5 to V7 . I start testing Identity Columns
    but we don't use the in Cobol programs yet .

    SQL reference recommends ALWAYS unless you are using data propagation.

    I believe in Cobol programs you must specify in the INSERT statement all the
    columns you need to inserted with their values except the Identity Column .
    DB2 will provide the value for this column

    A.Galaios

    DBA

    EFG EUROBANK ERGASIAS



    Disclaimer
    This e-mail is confidential. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete the copy from your system.
    EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any opinion, recommendation, conclusion, solicitation, offer or agreement or any information contained in this communication.
    EFG Eurobank Ergasias S.A. cannot accept any responsibility for the accuracy or completeness of this message as it has been transmitted over a public network. If you suspect that the message may have been intercepted or amended, please call the sender.



    Steve Grimes
    Hello. V7.1 OS/390 2.10 here. Here's a CICS snippet I put together this
    week. It basically confirms what someone else wrote -- leave the IDENTITY
    column out of the Insert statement.

    EXEC SQL
    DECLARE GLOBAL TEMPORARY TABLE USER_ACCT_TBL
    (ROW_IDENT INTEGER GENERATED BY DEFAULT
    AS IDENTITY,
    ACCT_DEPT CHAR(6) DEFAULT,
    ACCT_FUND CHAR(7) DEFAULT,
    ACCT_LEDGER CHAR(1) DEFAULT,
    ACCT_CLASS CHAR(1) DEFAULT,
    ACCT_SUB_CLASS CHAR(4) DEFAULT)
    ON COMMIT PRESERVE ROWS
    END-EXEC

    The insert is built dynamically....

    INSERT INTO SESSION.USER_ACCT_TBL (ACCT_DEPT, ACCT_FUND,
    ACCT_LEDGER, ACCT_CLASS, ACCT_SUB_CLASS)
    SELECT ALPR.ACCT_DEPT, ALPR.ACCT_FUND,
    ALPR.ACCT_LEDGER, ALPR.ACCT_CLASS, ALPR.ACCT_SUB_CLASS
    FROM FIT_ALL_PROF ALPR
    WHERE blah blah blah

    The only funny thing is, if I don't drop the GTT before exiting the
    program, DB2 drops like a stone. All 4 address spaces vanish. (Happened
    in Prod 3 times yesterday. Oooops.)

    Stg




    "Proctor,
    William" To: [login to unmask email]
    <William.Proctor@ cc:
    TGSLC.ORG> Subject: using Cobol and Identity columns.
    Sent by: DB2 Data
    Base Discussion
    List
    <[login to unmask email]
    LASSOC.COM>


    06/26/2003 10:59
    AM
    Please respond to
    DB2 Data Base
    Discussion List






    Does anyone have an example of using db2 idenity columns GENERATED ALWAYS
    AS IDENTITY in a cobol program.  I either get errors during the insert or
    on the bind depending on how I code the application.  This is our first
    attempt at this and I'm not finding very good documentation on it.


    Bill Proctor
    Database Administrator (Adabas/DB2)
    Texas Guaranteed Student Loan Corp.
    Austin, Texas
    Phone: 512-219-4847







    Susan Tipton
    We are running into a problem with identity columns and SAS. PROC SQL is inserting values into the identity column even when we do not specify it in the column list. Is there a work around? The SAS manual indicates that this is a feature of SAS.

    Susan

    Susan Ann Tipton
    DataBase Administrator
    Shands HealthCare
    [login to unmask email]
    (352) 265-0680 x85479
    (352) 265-7982 fax


    >>> [login to unmask email] 06/27/03 8:46:57 AM >>>
    Hello. V7.1 OS/390 2.10 here. Here's a CICS snippet I put together this
    week. It basically confirms what someone else wrote -- leave the IDENTITY
    column out of the Insert statement.

    EXEC SQL
    DECLARE GLOBAL TEMPORARY TABLE USER_ACCT_TBL
    (ROW_IDENT INTEGER GENERATED BY DEFAULT
    AS IDENTITY,
    ACCT_DEPT CHAR(6) DEFAULT,
    ACCT_FUND CHAR(7) DEFAULT,
    ACCT_LEDGER CHAR(1) DEFAULT,
    ACCT_CLASS CHAR(1) DEFAULT,
    ACCT_SUB_CLASS CHAR(4) DEFAULT)
    ON COMMIT PRESERVE ROWS
    END-EXEC

    The insert is built dynamically....

    INSERT INTO SESSION.USER_ACCT_TBL (ACCT_DEPT, ACCT_FUND,
    ACCT_LEDGER, ACCT_CLASS, ACCT_SUB_CLASS)
    SELECT ALPR.ACCT_DEPT, ALPR.ACCT_FUND,
    ALPR.ACCT_LEDGER, ALPR.ACCT_CLASS, ALPR.ACCT_SUB_CLASS
    FROM FIT_ALL_PROF ALPR
    WHERE blah blah blah

    The only funny thing is, if I don't drop the GTT before exiting the
    program, DB2 drops like a stone. All 4 address spaces vanish. (Happened
    in Prod 3 times yesterday. Oooops.)

    Stg




    "Proctor,
    William" To: [login to unmask email]
    <William.Proctor@ cc:
    TGSLC.ORG> Subject: using Cobol and Identity columns.
    Sent by: DB2 Data
    Base Discussion
    List
    <[login to unmask email]
    LASSOC.COM>


    06/26/2003 10:59
    AM
    Please respond to
    DB2 Data Base
    Discussion List






    Does anyone have an example of using db2 idenity columns GENERATED ALWAYS
    AS IDENTITY in a cobol program. I either get errors during the insert or
    on the bind depending on how I code the application. This is our first
    attempt at this and I'm not finding very good documentation on it.


    Bill Proctor
    Database Administrator (Adabas/DB2)
    Texas Guaranteed Student Loan Corp.
    Austin, Texas
    Phone: 512-219-4847





    ======================= DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
    Phil Grainger
    [BMC Software]
    Hi Shery

    Consider this

    You have a customer table that has the customer number (key) column defined as an identity column GENERATED ALWAYS
    It's like this in ALL of your DB2 environments

    Now try unloading all (or a sample) of your customers from your production subsystem and reloading them into development. You will get a whole new set of customer numbers! Now if you do the same for records from your order table you will have orders for customers where no customer with that customer number exist!

    GENERATED BY DEFAULT is much more flexible and does the same thing most of the time anyway (provided you train your developers to NOT supply values when they do their inserts)

    Phil Grainger
    Computer Associates
    Product Manager, DB2
    Tel: +44 (0)161 928 9334
    Fax: +44 (0)161 941 3775
    Mobile: +44 (0)7970 125 752
    [login to unmask email]



    -----Original Message-----
    From: HEPP SHERY C [mailto:[login to unmask email]
    Sent: Thursday, June 26, 2003 10:11 PM
    To: [login to unmask email]
    Subject: Re: [DB2-L] using Cobol and Identity columns.


    Phil- I'm curious about your statement that generated always identity columns cause problems. Would you care to elaborate on this? I have an app that uses both types of identity columns and in my experiences we've had more problems with the generated by default rather than the generated always. Just wondering what gotchas may be lurking out there in the future.

    Thanks- Regards, Shery Hepp

    -----Original Message-----
    From: Grainger, Phil [mailto:[login to unmask email]
    Sent: Thursday, June 26, 2003 9:11 AM
    To: [login to unmask email]
    Subject: Re: using Cobol and Identity columns.


    Bill

    They are just normal numeric columns as far as COBOL is concerned. On the insert DON'T specify a value. Also, consider GENERATED BY DEFAULT instead of GENERATED ALWAYS - this will cause less problems later


    Phil Grainger
    Computer Associates
    Product Manager, DB2
    Tel: +44 (0)161 928 9334
    Fax: +44 (0)161 941 3775
    Mobile: +44 (0)7970 125 752
    [login to unmask email]

    -----Original Message-----
    From: Proctor, William [mailto:[login to unmask email]
    Sent: Thursday, June 26, 2003 5:00 PM
    To: [login to unmask email]
    Subject: [DB2-L] using Cobol and Identity columns.



    Does anyone have an example of using db2 idenity columns GENERATED ALWAYS AS IDENTITY in a cobol program. I either get errors during the insert or on the bind depending on how I code the application. This is our first attempt at this and I'm not finding very good documentation on it.





    Bill Proctor

    Database Administrator (Adabas/DB2)

    Texas Guaranteed Student Loan Corp.

    Austin, Texas

    Phone: 512-219-4847





    ================




    Julian Stuhler
    [Triton Consulting]
    Susan,

    I'm not sure how much control you have over the SQL statement in a SAS
    environment, but you could try including the identity column in the
    column list, and using the DEFAULT clause in the values list. For
    example, if COL1 is an id column you can use the following syntax:

    INSERT INTO T1 (COL1, COL2, COL3)
    VALUES (DEFAULT,'col2_value', 'col3_value')

    Regards,

    Julian


    ______________________

    Julian Stuhler
    DB2 Specialist, IBM Gold Consultant
    Triton Consulting - The Database Experts

    E-mail: [login to unmask email]
    Web: http://www.triton.co.uk
    Mobile: +44 (0)7768 446927
    Personal Fax: +44 (0)870 1243510
    Office Tel: +44 (0)870 2411550
    Office Fax: +44 (0)870 2411549



    -----Original Message-----
    From: DB2 Data Base Discussion List [mailto:[login to unmask email]
    On Behalf Of Susan Tipton
    Sent: 27 June 2003 21:47
    To: [login to unmask email]
    Subject: Re: using Cobol and Identity columns.


    We are running into a problem with identity columns and SAS. PROC SQL
    is inserting values into the identity column even when we do not specify
    it in the column list. Is there a work around? The SAS manual
    indicates that this is a feature of SAS.

    Susan

    Susan Ann Tipton
    DataBase Administrator
    Shands HealthCare
    [login to unmask email]
    (352) 265-0680 x85479
    (352) 265-7982 fax


    >>> [login to unmask email] 06/27/03 8:46:57 AM >>>
    Hello. V7.1 OS/390 2.10 here. Here's a CICS snippet I put together
    this
    week. It basically confirms what someone else wrote -- leave the
    IDENTITY column out of the Insert statement.

    EXEC SQL
    DECLARE GLOBAL TEMPORARY TABLE USER_ACCT_TBL
    (ROW_IDENT INTEGER GENERATED BY DEFAULT
    AS IDENTITY,
    ACCT_DEPT CHAR(6) DEFAULT,
    ACCT_FUND CHAR(7) DEFAULT,
    ACCT_LEDGER CHAR(1) DEFAULT,
    ACCT_CLASS CHAR(1) DEFAULT,
    ACCT_SUB_CLASS CHAR(4) DEFAULT)
    ON COMMIT PRESERVE ROWS
    END-EXEC

    The insert is built dynamically....

    INSERT INTO SESSION.USER_ACCT_TBL (ACCT_DEPT, ACCT_FUND, ACCT_LEDGER,
    ACCT_CLASS, ACCT_SUB_CLASS) SELECT ALPR.ACCT_DEPT, ALPR.ACCT_FUND,
    ALPR.ACCT_LEDGER, ALPR.ACCT_CLASS, ALPR.ACCT_SUB_CLASS FROM
    FIT_ALL_PROF ALPR WHERE blah blah blah

    The only funny thing is, if I don't drop the GTT before exiting the
    program, DB2 drops like a stone. All 4 address spaces vanish.
    (Happened
    in Prod 3 times yesterday. Oooops.)

    Stg





    "Proctor,

    William" To:
    [login to unmask email]
    <William.Proctor@ cc:

    TGSLC.ORG> Subject: using Cobol and
    Identity columns.
    Sent by: DB2 Data

    Base Discussion

    List

    <[login to unmask email]

    LASSOC.COM>





    06/26/2003 10:59

    AM

    Please respond to

    DB2 Data Base

    Discussion List









    Does anyone have an example of using db2 idenity columns GENERATED
    ALWAYS AS IDENTITY in a cobol program. I either get errors during the
    insert or on the bind depending on how I code the application. This is
    our first attempt at this and I'm not finding very good documentation on
    it.


    Bill Proctor
    Database Administrator (Adabas/DB2)
    Texas Guaranteed Student Loan Corp.
    Austin, Texas
    Phone: 512-219-4847





    =======================To change your
    subscription options or to cancel your subscription visit the DB2-L
    webpage at http://listserv.ylassoc.com. The owners of the list can be





    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact