By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,585 Members | 2,235 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,585 IT Pros & Developers. It's quick & easy.

ROW_NUUMBER LOOP

P: n/a
I all I am trying to go into a loop to insert data into a new table,
the loop is required because the select genearte to much data and the
transaction log gets full.
So i found a way to get this solved by spliting the data into smaller
insert peaces like that

INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC)
SELECT * FROM (SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS)
AS NU WHERE R BETWEEN 1 AND 99999

the between will increase at each run from 100000....
SQLCODE = -117
SQLSTATE = 42802
SQLERRMC =
SQLMSG = SQL0117N The number of values assigned is not the same as
the number of specified or implied columns. SQLSTATE=42802

This does unfortunatly work because the returned columns are note the
same among as the one i want to insert... can i delete somehow the R
column???

Thanks in advance for your help.

Rgds Chris

Jul 4 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
cb*****@gmail.com wrote:
I all I am trying to go into a loop to insert data into a new table,
the loop is required because the select genearte to much data and the
transaction log gets full.
So i found a way to get this solved by spliting the data into smaller
insert peaces like that

INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC)
SELECT * FROM (SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS)
AS NU WHERE R BETWEEN 1 AND 99999

the between will increase at each run from 100000....
SQLCODE = -117
SQLSTATE = 42802
SQLERRMC =
SQLMSG = SQL0117N The number of values assigned is not the same as
the number of specified or implied columns. SQLSTATE=42802

This does unfortunatly work because the returned columns are note the
same among as the one i want to insert... can i delete somehow the R
column???
No, but you explicitly declare what columns to be used:

INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC)
SELECT GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC FROM (SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS)
AS NU WHERE R BETWEEN 1 AND 99999

/Lennart

Jul 4 '07 #2

P: n/a
On Jul 4, 9:37 am, Lennart <erik.lennart.jons...@gmail.comwrote:
cber...@gmail.com wrote:
I all I am trying to go into a loop to insert data into a new table,
the loop is required because the select genearte to much data and the
transaction log gets full.
So i found a way to get this solved by spliting the data into smaller
insert peaces like that
INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC)
SELECT * FROM (SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS)
AS NU WHERE R BETWEEN 1 AND 99999
the between will increase at each run from 100000....
SQLCODE = -117
SQLSTATE = 42802
SQLERRMC =
SQLMSG = SQL0117N The number of values assigned is not the same as
the number of specified or implied columns. SQLSTATE=42802
This does unfortunatly work because the returned columns are note the
same among as the one i want to insert... can i delete somehow the R
column???

No, but you explicitly declare what columns to be used:

INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC)
SELECT GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC FROM (SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS)
AS NU WHERE R BETWEEN 1 AND 99999

/Lennart

I just have 4 columns in the target table and the query return 5
columns with the temporaly R coliumn which I am only using to be able
to insert the over 1 millions rows in 100 000 rows bit by bit... I
have no use of the R colimn in the target table.

Thanks anyway for your remark I will keep it in mind in my coding, I
am not a DB2 Specialist and I am reading tones of manuals lately... :-)

Jul 4 '07 #3

P: n/a
cb*****@gmail.com wrote:
On Jul 4, 9:37 am, Lennart <erik.lennart.jons...@gmail.comwrote:
[...]
>No, but you explicitly declare what columns to be used:

INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC)
SELECT GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC FROM (SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS)
AS NU WHERE R BETWEEN 1 AND 99999

/Lennart


I just have 4 columns in the target table and the query return 5
columns with the temporaly R coliumn which I am only using to be able
to insert the over 1 millions rows in 100 000 rows bit by bit... I
have no use of the R colimn in the target table.
Not sure I follow, the query I provided return 4 columns which will fit
into the insert stmt. Do you think there is something wrong with the query?

Anyhow, in most cases it is concidered bad practise to use "select * ..."

/Lennart

[...]
Jul 4 '07 #4

P: n/a
On Jul 4, 8:21 pm, Lennart <erik.lennart.jons...@gmail.comwrote:
cber...@gmail.com wrote:
On Jul 4, 9:37 am, Lennart <erik.lennart.jons...@gmail.comwrote:
[...]
No, but you explicitly declare what columns to be used:
INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC)
SELECT GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC FROM (SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS)
AS NU WHERE R BETWEEN 1 AND 99999
/Lennart
I just have 4 columns in the target table and the query return 5
columns with the temporaly R coliumn which I am only using to be able
to insert the over 1 millions rows in 100 000 rows bit by bit... I
have no use of the R colimn in the target table.

Not sure I follow, the query I provided return 4 columns which will fit
into the insert stmt. Do you think there is something wrong with the query?

Anyhow, in most cases it is concidered bad practise to use "select * ..."

/Lennart

[...]
OOOhhhh sorry I did not pay enough attention and I did not saw that
you provided me with another sql script... but anyway it is not
working

INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC) SELECT GRACNAME, GRACCLNAME, GRACAUTHID, GRACACC FROM
(SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID, GRACC_ACCESS,
ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS) AS NU WHERE R
BETWEEN 1 AND 99999

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0206N "GRACNAME" is not valid in the context where it is used.
SQLSTATE=42703

SQL0206N
"GRACNAME
" is not valid in the context where it is used.

Jul 4 '07 #5

P: n/a
<cb*****@gmail.comwrote in message
news:11*********************@q75g2000hsh.googlegro ups.com...
>I all I am trying to go into a loop to insert data into a new table,
the loop is required because the select genearte to much data and the
transaction log gets full.
So i found a way to get this solved by spliting the data into smaller
insert peaces like that

INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC)
SELECT * FROM (SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS)
AS NU WHERE R BETWEEN 1 AND 99999

the between will increase at each run from 100000....
SQLCODE = -117
SQLSTATE = 42802
SQLERRMC =
SQLMSG = SQL0117N The number of values assigned is not the same as
the number of specified or implied columns. SQLSTATE=42802

This does unfortunatly work because the returned columns are note the
same among as the one i want to insert... can i delete somehow the R
column???

Thanks in advance for your help.

Rgds Chris
Hi Chris,

You could consider using a LOAD from a cursor to avoid log getting full:

declare c cursor for select ....
load from c of cursor insert into table [nonrecoverable|copy yes] option
.... in case your db is in archive log and you want to avoid your
tablespaces to be placed in backup pending.

HTH,

JM

Jul 4 '07 #6

P: n/a
cb*****@gmail.com wrote:
On Jul 4, 8:21 pm, Lennart <erik.lennart.jons...@gmail.comwrote:
>cber...@gmail.com wrote:
On Jul 4, 9:37 am, Lennart <erik.lennart.jons...@gmail.comwrote:
[...]
>No, but you explicitly declare what columns to be used:
>INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC)
SELECT GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC FROM (SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS)
AS NU WHERE R BETWEEN 1 AND 99999
>/Lennart
I just have 4 columns in the target table and the query return 5
columns with the temporaly R coliumn which I am only using to be able
to insert the over 1 millions rows in 100 000 rows bit by bit... I
have no use of the R colimn in the target table.

Not sure I follow, the query I provided return 4 columns which will fit
into the insert stmt. Do you think there is something wrong with the query?

Anyhow, in most cases it is concidered bad practise to use "select * ..."

/Lennart

[...]

OOOhhhh sorry I did not pay enough attention and I did not saw that
you provided me with another sql script... but anyway it is not
working

INSERT INTO D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC) SELECT GRACNAME, GRACCLNAME, GRACAUTHID, GRACACC FROM
(SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID, GRACC_ACCESS,
ROW_NUMBER() OVER() AS R FROM #RACADM.GENR_ACCESS) AS NU WHERE R
BETWEEN 1 AND 99999

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0206N "GRACNAME" is not valid in the context where it is used.
SQLSTATE=42703

SQL0206N
"GRACNAME
" is not valid in the context where it is used.

Sorry, I just pasted columns from the "insert" table. Look at the inner
select, what columns is in there? What columns do you think you should
select from NU?

select ?, ?. ?, ? from (
SELECT GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS, ROW_NUMBER() OVER() AS R
FROM #RACADM.GENR_ACCESS
) AS NU

As Jean-Marc mentioned, you can also use a load to avoid filling up the
logs. Yet another option is to import with commitcount

export to GENR_ACCESS.ixf of ixf
select GRACC_NAME, GRACC_CLASS_NAME, GRACC_AUTH_ID,
GRACC_ACCESS
from #RACADM.GENR_ACCESS;

import from GENR_ACCESS.ixf of ixf commitcount 100000
insert into D1.TCEODATAGRAC (GRACNAME, GRACCLNAME, GRACAUTHID,
GRACACC);

/Lennart

>
Jul 4 '07 #7

P: n/a
On Wed, 04 Jul 2007 22:34:02 +0200, Jean-Marc Blaise scribbled:
<cb*****@gmail.comwrote in message
news:11*********************@q75g2000hsh.googlegro ups.com...
>>I all I am trying to go into a loop to insert data into a new table,
the loop is required because the select genearte to much data and the
transaction log gets full.
[snip]
You could consider using a LOAD from a cursor to avoid log getting full:

declare c cursor for select ....
load from c of cursor insert into table [nonrecoverable|copy yes]
option ... in case your db is in archive log and you want to avoid
your tablespaces to be placed in backup pending.
That's certainly the way I'd recommend doing it. Alternatively, if you
want to avoid LOAD (with circular logging it's fine, but with archived
logging it can be a bit intimidating), you might want to consider using
EXPORT and IMPORT (unfortunately IMPORT doesn't support cursors as a
source, like LOAD does). For example:

EXPORT TO /tmp/DATA.IXF OF IXF
SELECT
GRACC_NAME,
GRACC_CLASS_NAME,
GRACC_AUTH_ID,
GRACC_ACCESS
FROM
#RACADM.GENR_ACCESS;

IMPORT FROM /tmp/DATA.IXF OF IXF
METHOD N (
GRACNAME,
GRACCLNAME,
GRACAUTHID,
GRACACC
)
COMMITCOUNT AUTOMATIC
INSERT INTO D1.TCEODATAGRAC (
GRACNAME,
GRACCLNAME,
GRACAUTHID,
GRACACC
);

Warning: if there are triggers on the target table, DON'T use COMMITCOUNT
AUTOMATIC (just use COMMITCOUNT with some number that'll never overflow
the transaction log).

HTH,

Dave.
Jul 4 '07 #8

P: n/a
On Jul 5, 1:46 am, Dave Hughes <d...@waveform.plus.comwrote:
On Wed, 04 Jul 2007 22:34:02 +0200, Jean-Marc Blaise scribbled:
<cber...@gmail.comwrote in message
news:11*********************@q75g2000hsh.googlegro ups.com...
>I all I am trying to go into a loop to insert data into a new table,
the loop is required because the select genearte to much data and the
transaction log gets full.
[snip]
You could consider using a LOAD from a cursor to avoid log getting full:
declare c cursor for select ....
load from c of cursor insert into table [nonrecoverable|copy yes]
option ... in case your db is in archive log and you want to avoid
your tablespaces to be placed in backup pending.

That's certainly the way I'd recommend doing it. Alternatively, if you
want to avoid LOAD (with circular logging it's fine, but with archived
logging it can be a bit intimidating), you might want to consider using
EXPORT and IMPORT (unfortunately IMPORT doesn't support cursors as a
source, like LOAD does). For example:

EXPORT TO /tmp/DATA.IXF OF IXF
SELECT
GRACC_NAME,
GRACC_CLASS_NAME,
GRACC_AUTH_ID,
GRACC_ACCESS
FROM
#RACADM.GENR_ACCESS;

IMPORT FROM /tmp/DATA.IXF OF IXF
METHOD N (
GRACNAME,
GRACCLNAME,
GRACAUTHID,
GRACACC
)
COMMITCOUNT AUTOMATIC
INSERT INTO D1.TCEODATAGRAC (
GRACNAME,
GRACCLNAME,
GRACAUTHID,
GRACACC
);

Warning: if there are triggers on the target table, DON'T use COMMITCOUNT
AUTOMATIC (just use COMMITCOUNT with some number that'll never overflow
the transaction log).

HTH,

Dave.

Thanks a lot all of you, I will see if I will be able to get the
necessary authorization (will have to work in a z/os env... RACF..)
and I did not mention it but this is running under rexx (cutomized to
run on windows and z/os as well).
I will have to take a close look on the API possibility.

Thanks a lot again, I really do appreciate
=:-))

Chris

Jul 5 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.