Posted: Tue Nov 08, 2005 3:44 pm Post subject: Do we need to populate length of varchar cols when inserting
Hi All,
I have a very basic doubt. Do we need to supply the length protion when we are inserting data into a table which has varchar coloumns thoruh a cobol db2 program. Even if we do not supply the length protion, will db2 calculates the length and stores in the first two bytes or it will default to the length specified for the coloumn.
Ravi,
I have been to this link, before posting. My concern is do we need to calculate the length specifically and then move into the length part , before inserting through a cobol program. I am not loading the table from a flat file. In such a case you need to have the length.
Kolusu,
In my current assignement, I have most of the coloumns defined as VARCHAR. I do not have any idea how the data looks like so that I can calculate the length of the coloumn before inserting into the database. I mean I am looking for some kind of delimiter so that I can calculate the length of the actual data using INSPECT verb. Is there a way if we do not know the delimiters, to calculate the length of the actual data or string?.
Is there any funtion exists in cobol 370, that calculated the string length?
I have tried length function, but it will give the length of the 01 variable, but not the actual content length.
Kolusu,
Thans for your inputs. I am trying to insert into the table. My concern is as i have many coloumns as VARCHAR, I want to find out a best way to calculate the lenght so that the same can be reused for everey coloumn with out any effect. I have tested with out supplying the length, its inserting nulls in to the column. After selecting the row from the column I put display for the length protion and its showing as zeros. when the length portion is populated, its inserting and displaying the length properly after selecting from the table.
Thanks for giving the link to calculate the length.This will do the trick for me.
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Wed Nov 09, 2005 1:35 pm Post subject:
I just ran a test in SPUFI, the results are below. Is a SPUFI Insert different thean COBOL?
(DB2 V8.1.5)
Code:
CREATE TABLE HOURS.UFCWLETDESC
(FROMDATE DATE NOT NULL,
TODATE DATE NOT NULL,
LETTERNAME CHAR(020) ,
LETTERDESC CHAR(100) ,
LETTERPATH VARCHAR(500) ,
IMIAGE CHAR(01) ,
USERID CHAR(40) ,
LASTUPDT TIMESTAMP ,
COLLATE CHAR(01) ,
CHILD_DOC CHAR(01) ,
RECIPIENT CHAR(50)
)
IN FDBHRS.FTSLDESC
;
INSERT INTO HOURS.UFCWLETDESC
(FROMDATE
,TODATE
,LETTERNAME
,LETTERDESC
,LETTERPATH
,IMIAGE
,USERID
,LASTUPDT
,COLLATE
,CHILD_DOC
,RECIPIENT
)
SELECT
'9999-12-31',
'9999-12-31',
'TESTN',
'TESTD',
'ABC123',
'Y',
'RON',
CURRENT TIMESTAMP,
'A',
'B',
'C'
FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+--------
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+--------
SELECT LENGTH(LETTERPATH), LETTERPATH
FROM HOURS.UFCWLETDESC
WHERE FROMDATE = '9999-12-31';
---------+---------+---------+---------+---------+---------+---
LETTERPATH
---------+---------+---------+---------+---------+---------+---
6 ABC123
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---
---------+---------+---------+---------+---------+---------+---
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
Nascar,
As suresh pointed out, SPUFI execution is a dynamic SQL. But from cobol program I am executing a static sql. if we do not populate the length field,we will end up getting -311 sql code.
Kolusu ,
Just to share with you, here is other way to get the length of a varchar variable.
NAME VARCAHR(30)
Code:
DCL GEN:
01 NAME
49 NAME-LEN PIC S9(4) USAGE COMP.
49 NAME-TEXT PIC X(30).
MOVE 40 (length of the text part)
TO NAME-LEN
EXEC SQL
SET :NAME = RTRIM(:NAME)
END-EXEC.
Then the text and len fileds will get populated with no spaces and the length of the string.
we can use the host variable :NAME while inserting into the table.
I did a select after this and displayed the length field, which comes out correct.
Will there be any overhead in performance if we use the code as above in compared to the code that you have provided to find the length of a sring using cobol.
I guess the one you provided will be faster than as said above. The number of high level calls would be more I guess in the above said case.
Please advice me.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
