Insert into nullable column

Scott Hodgin

Insert into nullable column
Dear list.

We just upgraded to DB2 6.1 from 5.1

I have table A with 23 columns
I have table B with the exact same 23 columns and one extra recently added
nullable column at the very end.

I have a program (package A) that worked fine under 5.1 where I inserted
into B selecting the columns from A.

To take advantage of the ability to rebalance partitions under V61, I
unloaded, dropped, created, reloaded a table that package A was dependent
upon so naturally it invalidated the package. When I went to rebind the
package, the bind failed on the insert with -117 stating that the number of
source columns was not equal to the number of target columns. I changed the
program to insert a hard-coded blank as the extra source column into the
nullable target column and the program bound fine.

Can someone explain to me why I should have to do this since one of the
great things is being able to add a nullable column at the end of a table on
the fly and no program changes should be necessary.

Thanks,
Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
Phone: (803) 936-4311 Fax: (803) 936-4629
[login to unmask email]



Tim Lowe

Re: Insert into nullable column
(in response to Scott Hodgin)
Scott,
Typically, I see this problem anytime someone does inserts without
specifying the column names that they are inserting, and a new column is
added to the table.
Therefore, I suspect that you are not specifying the column names in your
insert statement.
Am I correct?

I hope this helps.

Thanks,
Tim



"Hodgin,
Scott" To: [login to unmask email]
<[login to unmask email] cc:
INS.COM> Subject: Insert into nullable column
Sent by: DB2
Data Base
Discussion
List
<[login to unmask email]
OM>


12/27/2000
02:53 PM
Please
respond to
DB2 Data Base
Discussion
List






Dear list.

We just upgraded to DB2 6.1 from 5.1

I have table A with 23 columns
I have table B with the exact same 23 columns and one extra recently added
nullable column at the very end.

I have a program (package A) that worked fine under 5.1 where I inserted
into B selecting the columns from A.

To take advantage of the ability to rebalance partitions under V61, I
unloaded, dropped, created, reloaded a table that package A was dependent
upon so naturally it invalidated the package. When I went to rebind the
package, the bind failed on the insert with -117 stating that the number of
source columns was not equal to the number of target columns. I changed
the
program to insert a hard-coded blank as the extra source column into the
nullable target column and the program bound fine.

Can someone explain to me why I should have to do this since one of the
great things is being able to add a nullable column at the end of a table
on
the fly and no program changes should be necessary.

Thanks,
Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
Phone: (803) 936-4311 Fax: (803) 936-4629
[login to unmask email]








Sanjeev (CTS) S

Re: Insert into nullable column
(in response to Tim Lowe)
Hi,
If i can recall correctly then the nullable columns need not to be
mentioned in the INSERT INTO clause. DB2 will automatically insert NULL
value for that column.Please correct me if i am wrong !
Scott, look at the VALUE clause of the insert statement. In your case it is
INSERT into T1 SELECT * from T2. The error message says T2 should not have
more number of columns than T1. You can also specify the column names in the
SELECT.

HTH
Regards,
Sanjeev


> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, December 28, 2000 3:43 AM
> To: [login to unmask email]
> Subject: Re: Insert into nullable column
>
> Scott,
> Typically, I see this problem anytime someone does inserts without
> specifying the column names that they are inserting, and a new column is
> added to the table.
> Therefore, I suspect that you are not specifying the column names in your
> insert statement.
> Am I correct?
>
> I hope this helps.
>
> Thanks,
> Tim
>
>
>
> "Hodgin,
> Scott" To: [login to unmask email]
===
> Dear list.
>
> We just upgraded to DB2 6.1 from 5.1
>
> I have table A with 23 columns
> I have table B with the exact same 23 columns and one extra recently added
> nullable column at the very end.
>
> I have a program (package A) that worked fine under 5.1 where I inserted
> into B selecting the columns from A.
>
> To take advantage of the ability to rebalance partitions under V61, I
> unloaded, dropped, created, reloaded a table that package A was dependent
> upon so naturally it invalidated the package. When I went to rebind the
> package, the bind failed on the insert with -117 stating that the number
> of
> source columns was not equal to the number of target columns. I changed
> the
> program to insert a hard-coded blank as the extra source column into the
> nullable target column and the program bound fine.
>
> Can someone explain to me why I should have to do this since one of the
> great things is being able to add a nullable column at the end of a table
> on
> the fly and no program changes should be necessary.
>
> Thanks,
> Scott Hodgin, Database Administrator
> South Carolina Farm Bureau Insurance Company
> Phone: (803) 936-4311 Fax: (803) 936-4629
> [login to unmask email]
>
>
>
>
>
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Sanjeev (CTS) S

Re: Insert into nullable column
(in response to Sanjeev (CTS) S)
As i said , DB2 will automatically insert NULL in the nullable column, but i
realised what Tim wanted to mention that the column is not mentioned in the
INSERT INTO clause but it is there in the SELECT clause. This can also give
you sqlcode -117.

HTH
Regards,
Sanjeev

> -----Original Message-----
> From: S, Sanjeev (CTS)
> Sent: Thursday, December 28, 2000 5:24 PM
> To: 'DB2 Data Base Discussion List'
> Subject: RE: Insert into nullable column
>
> Hi,
> If i can recall correctly then the nullable columns need not to be
> mentioned in the INSERT INTO clause. DB2 will automatically insert NULL
> value for that column.Please correct me if i am wrong !
> Scott, look at the VALUE clause of the insert statement. In your case it
> is INSERT into T1 SELECT * from T2. The error message says T2 should not
> have more number of columns than T1. You can also specify the column names
> in the SELECT.
>
> HTH
> Regards,
> Sanjeev
>
>
> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, December 28, 2000 3:43 AM
> To: [login to unmask email]
> Subject: Re: Insert into nullable column
>
> Scott,
> Typically, I see this problem anytime someone does inserts without
> specifying the column names that they are inserting, and a new
> column is
> added to the table.
> Therefore, I suspect that you are not specifying the column names in
> your
> insert statement.
> Am I correct?
>
> I hope this helps.
>
> Thanks,
> Tim
>
>
>
> "Hodgin,
> Scott" To: [login to unmask email]
> ===
> Dear list.
>
> We just upgraded to DB2 6.1 from 5.1
>
> I have table A with 23 columns
> I have table B with the exact same 23 columns and one extra recently
> added
> nullable column at the very end.
>
> I have a program (package A) that worked fine under 5.1 where I
> inserted
> into B selecting the columns from A.
>
> To take advantage of the ability to rebalance partitions under V61,
> I
> unloaded, dropped, created, reloaded a table that package A was
> dependent
> upon so naturally it invalidated the package. When I went to rebind
> the
> package, the bind failed on the insert with -117 stating that the
> number of
> source columns was not equal to the number of target columns. I
> changed
> the
> program to insert a hard-coded blank as the extra source column into
> the
> nullable target column and the program bound fine.
>
> Can someone explain to me why I should have to do this since one of
> the
> great things is being able to add a nullable column at the end of a
> table
> on
> the fly and no program changes should be necessary.
>
> Thanks,
> Scott Hodgin, Database Administrator
> South Carolina Farm Bureau Insurance Company
> Phone: (803) 936-4311 Fax: (803) 936-4629
> [login to unmask email]
>
>
>
> visit
> the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
> list can
>
>
>
>
> visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
> list can
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Scott Hodgin

Re: Insert into nullable column
(in response to Sanjeev (CTS) S)
Actually, I am specifying the column names, but I really should not have to.
Insert into table B select * from table A should work fine as. It is my
understanding that as long as the source table has columns less than or
equal to the number of columns in the target table (matching data types, of
course) and that any additional target columns can allow nulls, the
statement should work.

Here's what gets me.

1). The program worked fine under V5.1
2). The program worked fine under V6.1 until the TS containing the table
was dropped and recreated. This invalidated the package. Upon rebind, the
bind fails with -117

Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
Phone: (803) 936-4311 Fax: (803) 936-4629
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
[login to unmask email]
Sent: Wednesday, December 27, 2000 5:13 PM
To: [login to unmask email]
Subject: Re: Insert into nullable column

Scott,
Typically, I see this problem anytime someone does inserts without
specifying the column names that they are inserting, and a new column is
added to the table.
Therefore, I suspect that you are not specifying the column names in your
insert statement.
Am I correct?

I hope this helps.

Thanks,
Tim



"Hodgin,
Scott" To: [login to unmask email]
<[login to unmask email] cc:
INS.COM> Subject: Insert into nullable
column
Sent by: DB2
Data Base
Discussion
List
<[login to unmask email]
OM>


12/27/2000
02:53 PM
Please
respond to
DB2 Data Base
Discussion
List






Dear list.

We just upgraded to DB2 6.1 from 5.1

I have table A with 23 columns
I have table B with the exact same 23 columns and one extra recently added
nullable column at the very end.

I have a program (package A) that worked fine under 5.1 where I inserted
into B selecting the columns from A.

To take advantage of the ability to rebalance partitions under V61, I
unloaded, dropped, created, reloaded a table that package A was dependent
upon so naturally it invalidated the package. When I went to rebind the
package, the bind failed on the insert with -117 stating that the number of
source columns was not equal to the number of target columns. I changed
the
program to insert a hard-coded blank as the extra source column into the
nullable target column and the program bound fine.

Can someone explain to me why I should have to do this since one of the
great things is being able to add a nullable column at the end of a table
on
the fly and no program changes should be necessary.

Thanks,
Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
Phone: (803) 936-4311 Fax: (803) 936-4629
[login to unmask email]













John Hardy

Re: Insert into nullable column
(in response to Mohammed Nayeem)
Sorry to contradict you and everyone else (except Tim), but the fact is:

The Insert into / Select from construct demands that the number of columns
that you are inserting must match the number that you are selecting from.
DB2 cannot 'automatically' do anything when they don't match.

I think the V5 to V6 transition is a red herring.

Consider the following two tables:

TAB1

TAB1_COL1 CHAR(1) NOT NULL
TAB1_COL2 CHAR(1)

TAB2

TAB2_COL1 CHAR(1) NOT NULL

The following queries are valid:

INSERT INTO TAB1 (TAB1_COL1)
SELECT TAB2_COL1 FROM TAB2

INSERT INTO TAB1 (TAB1_COL1)
SELECT * FROM TAB2

The following queries are invalid:

INSERT INTO TAB1
SELECT * FROM TAB2

INSERT INTO TAB1
SELECT TAB2_COL1 FROM TAB2

Of course, if you...

1. Create TAB1 with one column: TAB1_COL1.
2. Bind program PROG1 with embedded SQL consisting of any of the 4 queries
above.
3. Alter TAB1 to add TAB1_COL2.

...then your program will execute merrily until you try to rebind it, at
which point your rebind will fail.

As has been discussed in this forum before, the following embedded query in
PROG1 :

SELECT *
INTO :TAB1_COL1
FROM TAB1

will work after TAB1_COL2 has been added to TAB1 until PROG1 is
rebound.

I guess this is because the optimiser strips your query down and resolves
the '*' in this query, just as it resolves the number of columns to be
inserted into TAB1 from TAB2 in the Insert into / Select from construct
above.

We all know the golden rule - 'SELECT * is bad, never use it in embedded
SQL'. We also know the rule that we should specify a list of column names
when inserting. The same applies with 'INSERT INTO / SELECT FROM'. The only
safe syntax (using these tables as an example) is:

INSERT
INTO TAB1
(TAB1_COL1)
SELECT
TAB2_COL1
FROM TAB2


Hope this helps,

John Hardy




On Thu, 28 Dec 2000 07:02:35 -0600, Hodgin, Scott <[login to unmask email]>
wrote:

>Actually, I am specifying the column names, but I really should not have
to.
>Insert into table B select * from table A should work fine as. It is my
>understanding that as long as the source table has columns less than or
>equal to the number of columns in the target table (matching data types, of
>course) and that any additional target columns can allow nulls, the
>statement should work.
>
>Here's what gets me.
>
>1). The program worked fine under V5.1
>2). The program worked fine under V6.1 until the TS containing the table
>was dropped and recreated. This invalidated the package. Upon rebind, the
>bind fails with -117
>
>Scott Hodgin, Database Administrator
>South Carolina Farm Bureau Insurance Company
>Phone: (803) 936-4311 Fax: (803) 936-4629
>[login to unmask email]
>
> -----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
>[login to unmask email]
>Sent: Wednesday, December 27, 2000 5:13 PM
>To: [login to unmask email]
>Subject: Re: Insert into nullable column
>
>Scott,
>Typically, I see this problem anytime someone does inserts without
>specifying the column names that they are inserting, and a new column is
>added to the table.
>Therefore, I suspect that you are not specifying the column names in your
>insert statement.
>Am I correct?
>
>I hope this helps.
>
>Thanks,
> Tim
>
>
>
> "Hodgin,
> Scott" To: [login to unmask email]
> <[login to unmask email] cc:
> INS.COM> Subject: Insert into nullable
>column
> Sent by: DB2
> Data Base
> Discussion
> List
> <[login to unmask email]
> OM>
>
>
> 12/27/2000
> 02:53 PM
> Please
> respond to
> DB2 Data Base
> Discussion
> List
>
>
>
>
>
>
>Dear list.
>
>We just upgraded to DB2 6.1 from 5.1
>
>I have table A with 23 columns
>I have table B with the exact same 23 columns and one extra recently added
>nullable column at the very end.
>
>I have a program (package A) that worked fine under 5.1 where I inserted
>into B selecting the columns from A.
>
>To take advantage of the ability to rebalance partitions under V61, I
>unloaded, dropped, created, reloaded a table that package A was dependent
>upon so naturally it invalidated the package. When I went to rebind the
>package, the bind failed on the insert with -117 stating that the number of
>source columns was not equal to the number of target columns. I changed
>the
>program to insert a hard-coded blank as the extra source column into the
>nullable target column and the program bound fine.
>
>Can someone explain to me why I should have to do this since one of the
>great things is being able to add a nullable column at the end of a table
>on
>the fly and no program changes should be necessary.
>
>Thanks,
>Scott Hodgin, Database Administrator
>South Carolina Farm Bureau Insurance Company
>Phone: (803) 936-4311 Fax: (803) 936-4629
>[login to unmask email]
>
>
>
>
>
>
>
>
the
>
>
>
>
>





Mohammed Nayeem

Re: Insert into nullable column
(in response to Scott Hodgin)
DBRM problem ?? double check



Mohammed Nayeem

Re: Insert into nullable column
(in response to Mohammed Nayeem)
Double check INSERT stmt.
The number of insert values in the value list of the INSERT
statement is not the same as the number of object columns specified.



Scott Hodgin

Re: Insert into nullable column
(in response to John Hardy)
Thanks for the examples John.

The -117 is evidence that the rebind is demanding the extra value be
specified.

I guess I'm asking why DB2 can't 'automatically' figure out that, hey, I'm
giving you the first 23 values that match column for column. The target has
a 24th column that allows nulls and I'm not sending a 24th value. It just
seems to me that DB2 should be smart enough to just automatically insert the
null. Some part of DB2 (stage 1?) is happy to insert the null until the
rebind wakes up the optimizer, I guess.

Oh well, I'm off to make program changes...


Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
Phone: (803) 936-4311 Fax: (803) 936-4629
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
John Hardy
Sent: Thursday, December 28, 2000 9:45 AM
To: [login to unmask email]
Subject: Re: Insert into nullable column

Sorry to contradict you and everyone else (except Tim), but the fact is:

The Insert into / Select from construct demands that the number of columns
that you are inserting must match the number that you are selecting from.
DB2 cannot 'automatically' do anything when they don't match.

I think the V5 to V6 transition is a red herring.

Consider the following two tables:

TAB1

TAB1_COL1 CHAR(1) NOT NULL
TAB1_COL2 CHAR(1)

TAB2

TAB2_COL1 CHAR(1) NOT NULL

The following queries are valid:

INSERT INTO TAB1 (TAB1_COL1)
SELECT TAB2_COL1 FROM TAB2

INSERT INTO TAB1 (TAB1_COL1)
SELECT * FROM TAB2

The following queries are invalid:

INSERT INTO TAB1
SELECT * FROM TAB2

INSERT INTO TAB1
SELECT TAB2_COL1 FROM TAB2

Of course, if you...

1. Create TAB1 with one column: TAB1_COL1.
2. Bind program PROG1 with embedded SQL consisting of any of the 4 queries
above.
3. Alter TAB1 to add TAB1_COL2.

...then your program will execute merrily until you try to rebind it, at
which point your rebind will fail.

As has been discussed in this forum before, the following embedded query in
PROG1 :

SELECT *
INTO :TAB1_COL1
FROM TAB1

will work after TAB1_COL2 has been added to TAB1 until PROG1 is
rebound.

I guess this is because the optimiser strips your query down and resolves
the '*' in this query, just as it resolves the number of columns to be
inserted into TAB1 from TAB2 in the Insert into / Select from construct
above.

We all know the golden rule - 'SELECT * is bad, never use it in embedded
SQL'. We also know the rule that we should specify a list of column names
when inserting. The same applies with 'INSERT INTO / SELECT FROM'. The only
safe syntax (using these tables as an example) is:

INSERT
INTO TAB1
(TAB1_COL1)
SELECT
TAB2_COL1
FROM TAB2


Hope this helps,

John Hardy




On Thu, 28 Dec 2000 07:02:35 -0600, Hodgin, Scott <[login to unmask email]>
wrote:

>Actually, I am specifying the column names, but I really should not have
to.
>Insert into table B select * from table A should work fine as. It is my
>understanding that as long as the source table has columns less than or
>equal to the number of columns in the target table (matching data types, of
>course) and that any additional target columns can allow nulls, the
>statement should work.
>
>Here's what gets me.
>
>1). The program worked fine under V5.1
>2). The program worked fine under V6.1 until the TS containing the table
>was dropped and recreated. This invalidated the package. Upon rebind, the
>bind fails with -117
>
>Scott Hodgin, Database Administrator
>South Carolina Farm Bureau Insurance Company
>Phone: (803) 936-4311 Fax: (803) 936-4629
>[login to unmask email]
>
> -----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
>[login to unmask email]
>Sent: Wednesday, December 27, 2000 5:13 PM
>To: [login to unmask email]
>Subject: Re: Insert into nullable column
>
>Scott,
>Typically, I see this problem anytime someone does inserts without
>specifying the column names that they are inserting, and a new column is
>added to the table.
>Therefore, I suspect that you are not specifying the column names in your
>insert statement.
>Am I correct?
>
>I hope this helps.
>
>Thanks,
> Tim
>
>
>
> "Hodgin,
> Scott" To: [login to unmask email]
> <[login to unmask email] cc:
> INS.COM> Subject: Insert into nullable
>column
> Sent by: DB2
> Data Base
> Discussion
> List
> <[login to unmask email]
> OM>
>
>
> 12/27/2000
> 02:53 PM
> Please
> respond to
> DB2 Data Base
> Discussion
> List
>
>
>
>
>
>
>Dear list.
>
>We just upgraded to DB2 6.1 from 5.1
>
>I have table A with 23 columns
>I have table B with the exact same 23 columns and one extra recently added
>nullable column at the very end.
>
>I have a program (package A) that worked fine under 5.1 where I inserted
>into B selecting the columns from A.
>
>To take advantage of the ability to rebalance partitions under V61, I
>unloaded, dropped, created, reloaded a table that package A was dependent
>upon so naturally it invalidated the package. When I went to rebind the
>package, the bind failed on the insert with -117 stating that the number of
>source columns was not equal to the number of target columns. I changed
>the
>program to insert a hard-coded blank as the extra source column into the
>nullable target column and the program bound fine.
>
>Can someone explain to me why I should have to do this since one of the
>great things is being able to add a nullable column at the end of a table
>on
>the fly and no program changes should be necessary.
>
>Thanks,
>Scott Hodgin, Database Administrator
>South Carolina Farm Bureau Insurance Company
>Phone: (803) 936-4311 Fax: (803) 936-4629
>[login to unmask email]
>
>
>
>
>
>
>
>
the
>
>
>
>
>










Mohammed Nayeem

Re: Insert into nullable column
(in response to Scott Hodgin)
DB2 is not smart enough to just automatically insert the null value itself , but
it allows you to insert null value. So I think you should
send blank value to that 24th column.
Remove '*' from your sql stmt and re-frame this sql stmt
by selecting all columns rather than * .

If you are loading from dataset then you have to format this dataset
by including 24th column with null data or if you have loaded
succesfull , try updating this particlular column by null value (blank data)



Tim Lowe

Re: Insert into nullable column
(in response to Mohammed Nayeem)
Scott,
Could you send us the insert/subselect statement ?

Thanks,
Tim



Sanjeev (CTS) S

Re: Insert into nullable column
(in response to Tim Lowe)
Hi ,
I think what i mentioned to Scott in my previous two mails is to specify
something like this.

INSERT INTO B (23 out of 24 columns) SELECT * FORM A

23 out of 24 columns exclude the column in which NULL is allowed.This way
DB2 will automatically insert 24th column as NULL.

Sorry for not writing clearly in my first mail.

I hope i am correct now otherwise please correct me.

HTH
Regards
Sanjeev
> -----Original Message-----
> From: Hodgin, Scott [SMTP:[login to unmask email]
> Sent: Thursday, December 28, 2000 9:18 PM
> To: [login to unmask email]
> Subject: Re: Insert into nullable column
>
> Thanks for the examples John.
>
> The -117 is evidence that the rebind is demanding the extra value be
> specified.
>
> I guess I'm asking why DB2 can't 'automatically' figure out that, hey, I'm
> giving you the first 23 values that match column for column. The target
> has
> a 24th column that allows nulls and I'm not sending a 24th value. It just
> seems to me that DB2 should be smart enough to just automatically insert
> the
> null. Some part of DB2 (stage 1?) is happy to insert the null until the
> rebind wakes up the optimizer, I guess.
>
> Oh well, I'm off to make program changes...
>
>
> Scott Hodgin, Database Administrator
> South Carolina Farm Bureau Insurance Company
> Phone: (803) 936-4311 Fax: (803) 936-4629
> [login to unmask email]
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
> Of
> John Hardy
> Sent: Thursday, December 28, 2000 9:45 AM
> To: [login to unmask email]
> Subject: Re: Insert into nullable column
>
> Sorry to contradict you and everyone else (except Tim), but the fact is:
>
> The Insert into / Select from construct demands that the number of columns
> that you are inserting must match the number that you are selecting from.
> DB2 cannot 'automatically' do anything when they don't match.
>
> I think the V5 to V6 transition is a red herring.
>
> Consider the following two tables:
>
> TAB1
>
> TAB1_COL1 CHAR(1) NOT NULL
> TAB1_COL2 CHAR(1)
>
> TAB2
>
> TAB2_COL1 CHAR(1) NOT NULL
>
> The following queries are valid:
>
> INSERT INTO TAB1 (TAB1_COL1)
> SELECT TAB2_COL1 FROM TAB2
>
> INSERT INTO TAB1 (TAB1_COL1)
> SELECT * FROM TAB2
>
> The following queries are invalid:
>
> INSERT INTO TAB1
> SELECT * FROM TAB2
>
> INSERT INTO TAB1
> SELECT TAB2_COL1 FROM TAB2
>
> Of course, if you...
>
> 1. Create TAB1 with one column: TAB1_COL1.
> 2. Bind program PROG1 with embedded SQL consisting of any of the 4 queries
> above.
> 3. Alter TAB1 to add TAB1_COL2.
>
> ...then your program will execute merrily until you try to rebind it, at
> which point your rebind will fail.
>
> As has been discussed in this forum before, the following embedded query
> in
> PROG1 :
>
> SELECT *
> INTO :TAB1_COL1
> FROM TAB1
>
> will work after TAB1_COL2 has been added to TAB1 until PROG1 is
> rebound.
>
> I guess this is because the optimiser strips your query down and resolves
> the '*' in this query, just as it resolves the number of columns to be
> inserted into TAB1 from TAB2 in the Insert into / Select from construct
> above.
>
> We all know the golden rule - 'SELECT * is bad, never use it in embedded
> SQL'. We also know the rule that we should specify a list of column names
> when inserting. The same applies with 'INSERT INTO / SELECT FROM'. The
> only
> safe syntax (using these tables as an example) is:
>
> INSERT
> INTO TAB1
> (TAB1_COL1)
> SELECT
> TAB2_COL1
> FROM TAB2
>
>
> Hope this helps,
>
> John Hardy
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Sanjeev (CTS) S

Re: Insert into nullable column
(in response to Sanjeev (CTS) S)
Hi,
Let us forget the question of Scott for one moment and i would say DB2 is
"highly smart" to insert the null value but when we are writing the query it
should make some sense to DB2 as well.
So DB2 will insert the null value in the column in the cases mentioned
below :
Let's say T1 has 5 columns c1,c2,c3,c4 and c5 , where c5 is nullable.

1) we are writing the insert query something like this INSERT INTO T1
(c1,c2,c3,c4) Values(:c1,:c2,:c3,:c4). In this way DB2 will automatically
insert null for c5.
2) Assume there is other table T2 which has columns c1,c2,c3 and c4 (Scott's
case). We can write the query something like this
INSERT INTO T1(c1,c2,c3,c4) select * from T2.

If i am correct then there is need to specify the columns name in INSERT
INTO rather than value or select clause.

HTH
Regards,
Sanjeev
> -----Original Message-----
> From: Mohammed Nayeem [SMTP:[login to unmask email]
> Sent: Thursday, December 28, 2000 9:28 PM
> To: [login to unmask email]
> Subject: Re: Insert into nullable column
>
> DB2 is not smart enough to just automatically insert the null value itself
> , but
> it allows you to insert null value. So I think you should
> send blank value to that 24th column.
> Remove '*' from your sql stmt and re-frame this sql stmt
> by selecting all columns rather than * .
>
> If you are loading from dataset then you have to format this dataset
> by including 24th column with null data or if you have loaded
> succesfull , try updating this particlular column by null value (blank
> data)
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



James Campbell

Re: Insert into nullable column
(in response to Sanjeev (CTS) S)
If you read the SQL reference manual you will find the following things
documented
- SELECT * means all the columns in the from tables - at the time the
statement is prepared
- INSERT without a column list means all the columns in the table being
inserted into - also at the time the statement is prepared
- the number of columns in the INSERT column list must match the number of
columns in the SELECT list.

So, in the original situation, after a column was added to the INSERT table,
but not the FROM table, the number of columns used in the insert still
matched - until the statement was re-prepared, when the number of columns
was re-evaluated and they no longer matched.

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************



Scott Trometer

Re: Insert into nullable column
(in response to James Campbell)
Scott,

Getting off on a little bit of a tangent from your question....but, how were
you able to add a nullable column to the end of your table in V5? My
understanding is that you would have had to drop the table to be able to do
this, which, as you said, would invalidate the plans. So, wouldn't you have
had to rebind these plans to execute them under v5 too?

With that as the basis, I am assuming one of 3 things happened with Program
A:

a) rebind worked
b) rebind did not work
c) program A didn't actually execute under v5

If a), then I think you might have a valid concern between versions.


Regards,
Scott


-----Original Message-----
From: Hodgin, Scott [mailto:[login to unmask email]
Sent: Wednesday, December 27, 2000 3:54 PM
To: [login to unmask email]
Subject: Insert into nullable column


Dear list.

We just upgraded to DB2 6.1 from 5.1

I have table A with 23 columns
I have table B with the exact same 23 columns and one extra recently added
nullable column at the very end.

I have a program (package A) that worked fine under 5.1 where I inserted
into B selecting the columns from A.

To take advantage of the ability to rebalance partitions under V61, I
unloaded, dropped, created, reloaded a table that package A was dependent
upon so naturally it invalidated the package. When I went to rebind the
package, the bind failed on the insert with -117 stating that the number of
source columns was not equal to the number of target columns. I changed the
program to insert a hard-coded blank as the extra source column into the
nullable target column and the program bound fine.

Can someone explain to me why I should have to do this since one of the
great things is being able to add a nullable column at the end of a table on
the fly and no program changes should be necessary.

Thanks,
Scott Hodgin, Database Administrator
South Carolina Farm Bureau Insurance Company
Phone: (803) 936-4311 Fax: (803) 936-4629
[login to unmask email]





John Hardy

Re: Insert into nullable column
(in response to Scott Trometer)
Hello Scott (the latest one!),

You can ALTER ADD a column with the NULL (default), or NOT NULL WITH
DEFAULT. The only one you can't add is NOT NULL.


On Fri, 29 Dec 2000 07:30:21 -0500, Scott Trometer <[login to unmask email]>
wrote:

>Scott,
>
>Getting off on a little bit of a tangent from your question....but, how
were
>you able to add a nullable column to the end of your table in V5? My
>understanding is that you would have had to drop the table to be able to do
>this, which, as you said, would invalidate the plans. So, wouldn't you
have
>had to rebind these plans to execute them under v5 too?
>
>With that as the basis, I am assuming one of 3 things happened with Program
>A:
>
>a) rebind worked
>b) rebind did not work
>c) program A didn't actually execute under v5
>
>If a), then I think you might have a valid concern between versions.
>
>
>Regards,
>Scott
>
>
>-----Original Message-----
>From: Hodgin, Scott [mailto:[login to unmask email]
>Sent: Wednesday, December 27, 2000 3:54 PM
>To: [login to unmask email]
>Subject: Insert into nullable column
>
>
>Dear list.
>
>We just upgraded to DB2 6.1 from 5.1
>
>I have table A with 23 columns
>I have table B with the exact same 23 columns and one extra recently added
>nullable column at the very end.
>
>I have a program (package A) that worked fine under 5.1 where I inserted
>into B selecting the columns from A.
>
>To take advantage of the ability to rebalance partitions under V61, I
>unloaded, dropped, created, reloaded a table that package A was dependent
>upon so naturally it invalidated the package. When I went to rebind the
>package, the bind failed on the insert with -117 stating that the number of
>source columns was not equal to the number of target columns. I changed
the
>program to insert a hard-coded blank as the extra source column into the
>nullable target column and the program bound fine.
>
>Can someone explain to me why I should have to do this since one of the
>great things is being able to add a nullable column at the end of a table
on
>the fly and no program changes should be necessary.
>
>Thanks,
>Scott Hodgin, Database Administrator
>South Carolina Farm Bureau Insurance Company
>Phone: (803) 936-4311 Fax: (803) 936-4629
>[login to unmask email]
>
>
>
the
>
>
>