using Cobol and Identity columns.

William Proctor

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





Phil Grainger

Re: using Cobol and Identity columns.
(in response to William Proctor)
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

Re: using Cobol and Identity columns.
(in response to Phil Grainger)
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

Re: using Cobol and Identity columns.
(in response to HEPP SHERY C)
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

Re: using Cobol and Identity columns.
(in response to Antonis Galaios)
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

Re: using Cobol and Identity columns.
(in response to Steve Grimes)
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

Re: using Cobol and Identity columns.
(in response to Susan Tipton)
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

Re: using Cobol and Identity columns.
(in response to Phil Grainger)
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