Passing values between columns and host variable

Michael Kaplan

Passing values between columns and host variable

Dear List,

I'd like to hear your opinion about the following scenario :

Program A (written in COBOL) has host variable V1 which fits DB2 table (T1) column C1.

Then the former table T1 is droped and new table (the same name T1) has created with
enlarged column C1. F.e., former C1 was DECIMAL (5) and new one - DECIMAL (7).

Now, I run (without pre-compilation) program A again (after package rebinding manually
or automatically) and I got the following :

a) SQLCODE -304 when real value of re-newed column C1 does not fit host variable
definition and the truncation is needed.

b) O.K. when real value is within former column C1 definition (in my case 5 significant digits).

My questions are :

1) If REBIND produced no errors - that means that no column length specification is kept
in DBRM. Am I right ?

2) Does DB2 so sofisticate to pass real value (as I experementally checked for packed
columns) for all other types (numeric and char) ?

3) What about other languages (PL1, Assembler, C ) ?

Thanks for advance.

--
Michael Kaplan
DBA
Sapiens Technologies, Israel
Tel: 972-8-938-2830
E-mail: [login to unmask email]





Terry Purcell

Re: Passing values between columns and host variable
(in response to Michael Kaplan)
Michael,

Good questions. So good, I'll admit that I don't know the correct answers.
Hopefully, my attempt to answer will spark some interest from those that do.

1) Rebind does not produce an error because a datatype or length mismatch is
still a valid access path (just not a good one generally). Precompile would
consider this to be an error.

2) The DBRM must contain a reference to the host variable lengths, otherwise
the optimizer would not know that a datatype or length mismatch exists.

3) The DBRM should be independant of the programming language, ie. the same
applies for all languages.

Just my opinions (not fact).
Regards,
Terry Purcell
CPT Consulting

> -----Original Message-----
> From: Michael Kaplan [SMTP:[login to unmask email]
> Sent: Friday, 24 December 1999 1:52 am
> To: [login to unmask email]
> Subject: Passing values between columns and host variable
>
>
> Dear List,
>
> I'd like to hear your opinion about the following scenario :
>
> Program A (written in COBOL) has host variable V1 which fits DB2 table
> (T1) column C1.
>
> Then the former table T1 is droped and new table (the same name T1) has
> created with
> enlarged column C1. F.e., former C1 was DECIMAL (5) and new one -
> DECIMAL (7).
>
> Now, I run (without pre-compilation) program A again (after package
> rebinding manually
> or automatically) and I got the following :
>
> a) SQLCODE -304 when real value of re-newed column C1 does not fit host
> variable
> definition and the truncation is needed.
>
> b) O.K. when real value is within former column C1 definition (in my case
> 5 significant digits).
>
> My questions are :
>
> 1) If REBIND produced no errors - that means that no column length
> specification is kept
> in DBRM. Am I right ?
>
> 2) Does DB2 so sofisticate to pass real value (as I experementally checked
> for packed
> columns) for all other types (numeric and char) ?
>
> 3) What about other languages (PL1, Assembler, C ) ?
>
> Thanks for advance.
>
> --
> Michael Kaplan
> DBA
> Sapiens Technologies, Israel
> Tel: 972-8-938-2830
> E-mail: [login to unmask email]
>
>
>
>
>
>
>



[login to unmask email]

Re: Passing values between columns and host variable
(in response to Terry Purcell)
1) The data type and length have to be the content of the DBRM which helps
optimizer to decide whether it is a stage-2 or stage-1 predicate. Sqlcode
of -304 is returned when the actual data extraction takes place during the
run time.I have done a small experiment with a sample COBOL program and
found that precompiler also doesn't notice the data type and length
mismatch and it doesn't give an error.
2) For the data length mismatch it gives -304 and for data type mismatch it
gives -303.
3) Yes, the DBRM and Plan is independent of the programming language.

Sanjeev





"Purcell, Terry" <[login to unmask email]>@RYCI.COM> on
12/29/99 06:33:55 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: Passing values between columns and host variable


Michael,

Good questions. So good, I'll admit that I don't know the correct answers.
Hopefully, my attempt to answer will spark some interest from those that
do.

1) Rebind does not produce an error because a datatype or length mismatch
is
still a valid access path (just not a good one generally). Precompile would
consider this to be an error.

2) The DBRM must contain a reference to the host variable lengths,
otherwise
the optimizer would not know that a datatype or length mismatch exists.

3) The DBRM should be independant of the programming language, ie. the same
applies for all languages.

Just my opinions (not fact).
Regards,
Terry Purcell
CPT Consulting

> -----Original Message-----
> From: Michael Kaplan [SMTP:[login to unmask email]
> Sent: Friday, 24 December 1999 1:52 am
> To: [login to unmask email]
> Subject: Passing values between columns and host variable
>
>
> Dear List,
>
> I'd like to hear your opinion about the following scenario :
>
> Program A (written in COBOL) has host variable V1 which fits DB2 table
> (T1) column C1.
>
> Then the former table T1 is droped and new table (the same name T1) has
> created with
> enlarged column C1. F.e., former C1 was DECIMAL (5) and new one -
> DECIMAL (7).
>
> Now, I run (without pre-compilation) program A again (after package
> rebinding manually
> or automatically) and I got the following :
>
> a) SQLCODE -304 when real value of re-newed column C1 does not fit host
> variable
> definition and the truncation is needed.
>
> b) O.K. when real value is within former column C1 definition (in my
case
> 5 significant digits).
>
> My questions are :
>
> 1) If REBIND produced no errors - that means that no column length
> specification is kept
> in DBRM. Am I right ?
>
> 2) Does DB2 so sofisticate to pass real value (as I experementally
checked
> for packed
> columns) for all other types (numeric and char) ?
>
> 3) What about other languages (PL1, Assembler, C ) ?
>
> Thanks for advance.
>
> --
> Michael Kaplan
> DBA
> Sapiens Technologies, Israel
> Tel: 972-8-938-2830
> E-mail: [login to unmask email]
>
>
>
>
>
> the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can
>