MVSFORUMS.com

[deeptik]

While using embedded sql in cobol application program to insert data into a varchar field , is it necessary to populate the length host variable as well..or does Db2 automatically store the actual length ( i.e without the trailing spaces )..I have the same question in case of Updating a varchar field with embedded sql.

[dbzTHEdinosauer]

you always need to populate the lvl 49 length field if you are supplying the source data.
_________________
Dick Brenholtz
American living in Varel, Germany

[deeptik]

ok..but why is it necessary ? Will the query fail if we don't populate the lvl 49 length variable ?

Also , again if the insert/update is done outside the application program .i.e say through QMF it may not be possible to provide the length so I guess DB2 would automatically take care to store the actual length of the varchar field ?

Thanks

[jsharon1248]

You must provide the length on INSERT and UPDATE when using static SQL. If you don't, DB2 will use whatever residual value is stored in the length variable. If you never populate the length, that residual value will be 0, and that column will wind up with 0 length for all rows processed. To your point about QMF. QMF is a DB2 reporting tool. When you INSERT/UPDATE varchar columns using QMF, QMF utilizes dynamic SQL to PREPARE and EXECUTE the statements (probably EXECUTE IMMEDIATE, but I'm not certain). DB2 handles the lengths because it's dynamic SQL.

[CraigG]

SPUFI and QMF populate the length based on the delimiters for the character variables. It a program there are no delimiters used and the host variable field may be much longer then the value contained in it. Trailing characters in the host variable may or may not be meaningful. DB2 has no way of knowing if they are meaningful.

[deeptik]

Thanks for your replies, I think what I understood is that you will need to tell Db2 what length needs to be stored in case of static SQL.

I have a couple of more q's, I could have tried out myself but since I don't have access to compile Db2 test programs in my installation I will put them in this forum..

1) EMP TABLE (ENAME VARCHAR(50)
EID CHAR(7));

Corresponding dclgen

10 ENAME.
49 ENAME-LEN PIC S9(4) USAGE COMP.
49 ENAME-TEXT PIC X(50).
10 EID pic x(7).

If I need to insert Name = 'JOHN' and if I populate ENAME-TEXT='JOHN' and

1)ENAME-LEN = 4, value of ENAME stored in my table = 'JOHN'
2)ENAME-LEN = 3, will ENAME stored = 'JOH' or 'JOHN'
3)ENAME-LEN =0 (i.e I never populate this length) , what will be the value of ENAME ?

Basically, does value populated in length host variable determine the content of TEXT to be stored ( Well I guess the answer is a NO but still want to confirm) or is it only that you store the length for your own reference and and if it is stored wrongly DB2 can actually do nothing about it ?

2) Update condition. Suppose my current value in the database before update is 'JOHN'. Now I want to update 'JOHN' to 'IAN' but I forget to update the length host variable , what will be the value now in my database...will it be 'JIAN' ?

[jsharon1248]

1) correct, ENAME='JOHN' after the update
2) ENAME='JOH'
3) ENAME='', that is, a zero length string; this is not the same as null

Again, the program determines the length that DB2 uses for the column value. If you specify 3 and the entire 50 characters of the text variable are populated, DB2 will store the first 3 in the target column.

2) If you forget to update the length variable, DB2 will use whatever value is in the length variable at the time you issue the update. DB2 doesn't know you forgot. So the answer depends on what is in the length and text variables when you issue the update.

[deeptik]

Thank you so much Sharon ! I am clear on this now...