Dear all,

I am a programmer coding COBOL program on DB2 OS/390
Last week we drop one table and
create the table with the same name ,
but different table layout ,
we add a new column at the last column

As my co-worker and me known,
we need to recompile, link and re-bind all the programs that using this
table.

but we found something strange is
Some program we didnot do above step, then submit the program to run .
the plan will auto rebind when we submit..
but some program could not auto rebind ,,
the job return code is 693.

after we rebind those programs that return code is 693
we re-submit those program, all of them is rc= 00,

Does anyone know when need to re-bind and when the program autobind
themselves??
Do we miss any SYSIBM.??? DB2 system table setting ??
or we need to do some command (ex: grant ......) ?

Thanks for your kindly help

Peggy Wu

Hi Peggy,

so you changed some column specifications and added a new column at the
end?
I think a program not using any of the changed columns will autobind, any
other will require a bind/rebind.

Regards Rolf

"Peggy Wu" <rtomato.tw@yahoo.com.tw> wrote in message
news:bmeh4e$plm@netnews.hinet.net...
> Dear all,
>
> I am a programmer coding COBOL program on DB2 OS/390
> Last week we drop one table and
> create the table with the same name ,
> but different table layout ,
> we add a new column at the last column
>
> As my co-worker and me known,
> we need to recompile, link and re-bind all the programs that using
this
> table.
>
> but we found something strange is
> Some program we didnot do above step, then submit the program to run .
> the plan will auto rebind when we submit..
> but some program could not auto rebind ,,
> the job return code is 693.
>
> after we rebind those programs that return code is 693
> we re-submit those program, all of them is rc= 00,
>
> Does anyone know when need to re-bind and when the program autobind
> themselves??
> Do we miss any SYSIBM.??? DB2 system table setting ??
> or we need to do some command (ex: grant ......) ?
>
> Thanks for your kindly help
>
> Peggy Wu
>
Let's assume that the existing programs don't use the new column and that
none of them do a select * from the table (all column names are explicitly
named).

When the table is dropped, all plans that use that table are marked as
invalid. The first program that executes the program with an invalid plan
will attempt an automatic rebind. If the table has been re-created (with one
extra column), then the rebind will succeed and the program will function as
normal.

If the auto-rebind fails (the new table has not been re-created yet) then
the plan is marked as inoperative and the program will not execute and an
explicit bind or rebind must be executed by a DBA or programmer (after the
table is re-created).

I am not sure what caused the -693. If it had select * that might cause it.
Or maybe the table and indexes were in the process of being dropped when the
auto-rebind was attempted. Look at the code and see if there is anything
different in the SQL of those programs.

"Mark A" <ma@switchboard.net> wrote in message

Correction to above post

"The first program that executes the program with an invalid plan
will attempt an automatic rebind."

Should read:

"The first time a program with an invalid plan is executed, DB2
will attempt an automatic rebind."