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

INSERT Trouble

P: n/a
Hi all,

I have a strange (at least for me) things happening. I am save the
result of an outer join into a file and I read this file and put these
values into a table.
The first row written contains the code page... how comes did I do
something wrong or is it a normal behavior?
I have to say that I did not manage to insert the value directly from
outer join....... I am not a DB2 Specialist.

Thanks for your help

Jun 21 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
cb*****@gmail.com wrote:
Hi all,

I have a strange (at least for me) things happening. I am save the
result of an outer join into a file and I read this file and put these
values into a table.
The first row written contains the code page... how comes did I do
something wrong or is it a normal behavior?
I have no clue what "row contains the codepage" means in this context
Taking a wild guess you may want to consider the modifiers on EXPRT/LOAD
that suppress code-page conversion.
I have to say that I did not manage to insert the value directly from
outer join....... I am not a DB2 Specialist.
INSERT INTO T SELECT * FROM S LEFT OUTER JOIN V WHERE S.C1 = V.C1

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 21 '07 #2

P: n/a
On Jun 21, 1:10 pm, Serge Rielau <srie...@ca.ibm.comwrote:
cber...@gmail.com wrote:
Hi all,
I have a strange (at least for me) things happening. I am save the
result of an outer join into a file and I read this file and put these
values into a table.
The first row written contains the code page... how comes did I do
something wrong or is it a normal behavior?

I have no clue what "row contains the codepage" means in this context
Taking a wild guess you may want to consider the modifiers on EXPRT/LOAD
that suppress code-page conversion.
I have to say that I did not manage to insert the value directly from
outer join....... I am not a DB2 Specialist.

INSERT INTO T SELECT * FROM S LEFT OUTER JOIN V WHERE S.C1 = V.C1

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Thanks Serge so let me explain...

I run this sql:
SELECT DISTINCT TSOUNAME, TSOPRGNAME FROM D1.TCEOQRYALLHPUGRP LEFT
OUTER JOIN
D1.TCEODATAUSTSO ON (TSOUNAME = QRYALLGRPHPUUS)

THEY ARE 2336 ROWS IN TCEODATAUSTSO AND 168332 ROWS IN
TCEOQRYALLHPUGRP SO I WANT TO SAVE THE RESULT INTO A NEW TABLE
TCEOHPUU (EXTRACT FROM A REXX) COL1 AND COL2 CONTAINS TSOUNAME
TSOPRGNAME

STATEMENT ="INSERT INTO D1.TCEOHPUU VALUES ('"COL1"','"COL2"')
INTERPRET SQQ "EXECUTE
IMMEDIATE :STATEMENT"
IF (SQLCA.SQLCODE <'0') & (SQLCA.SQLCODE <'-803')
THEN
CALL SQLCA "INPUTMETAHPU: INSERT STEP
FAILED"
INTERPRET FURZ
"COMMIT"
AND THAN I GET THIS RESULT

COL1 COL2
1208 1208 <-------------------- First row (when DB code page
UFT-8)
1252 1252 <-------------------- First row (when DB code
page Latin)

I LITTLE CLEARER NOW... ?

THANKS FOR YOUR HELP

Jun 22 '07 #3

P: n/a
cb*****@gmail.com wrote:
On Jun 21, 1:10 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>cber...@gmail.com wrote:
>>Hi all,
I have a strange (at least for me) things happening. I am save the
result of an outer join into a file and I read this file and put these
values into a table.
The first row written contains the code page... how comes did I do
something wrong or is it a normal behavior?
I have no clue what "row contains the codepage" means in this context
Taking a wild guess you may want to consider the modifiers on EXPRT/LOAD
that suppress code-page conversion.
>>I have to say that I did not manage to insert the value directly from
outer join....... I am not a DB2 Specialist.
INSERT INTO T SELECT * FROM S LEFT OUTER JOIN V WHERE S.C1 = V.C1

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks Serge so let me explain...

I run this sql:
SELECT DISTINCT TSOUNAME, TSOPRGNAME FROM D1.TCEOQRYALLHPUGRP LEFT
OUTER JOIN
D1.TCEODATAUSTSO ON (TSOUNAME = QRYALLGRPHPUUS)

THEY ARE 2336 ROWS IN TCEODATAUSTSO AND 168332 ROWS IN
TCEOQRYALLHPUGRP SO I WANT TO SAVE THE RESULT INTO A NEW TABLE
TCEOHPUU (EXTRACT FROM A REXX) COL1 AND COL2 CONTAINS TSOUNAME
TSOPRGNAME

STATEMENT ="INSERT INTO D1.TCEOHPUU VALUES ('"COL1"','"COL2"')
INTERPRET SQQ "EXECUTE
IMMEDIATE :STATEMENT"
IF (SQLCA.SQLCODE <'0') & (SQLCA.SQLCODE <'-803')
THEN
CALL SQLCA "INPUTMETAHPU: INSERT STEP
FAILED"
INTERPRET FURZ
"COMMIT"
AND THAN I GET THIS RESULT

COL1 COL2
1208 1208 <-------------------- First row (when DB code page
UFT-8)
1252 1252 <-------------------- First row (when DB code
page Latin)

I LITTLE CLEARER NOW... ?

THANKS FOR YOUR HELP
Not a bit. But that may be because I don't know REXX.
I would look at the variable binding. For some reason you are extracting
the codepage instead of the values.
The issue very likely on the select (cursor) side and not the INSERT.
Now, why you are using a cursor with single row inserts it beyond me...

Cheers
Serge

PS: In general in the net using CAPS is considered shouting. This group
is fairy mellow w.r.t. netiquette, others are not....

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 22 '07 #4

P: n/a
On Jun 22, 1:24 pm, Serge Rielau <srie...@ca.ibm.comwrote:
cber...@gmail.com wrote:
On Jun 21, 1:10 pm, Serge Rielau <srie...@ca.ibm.comwrote:
cber...@gmail.com wrote:
Hi all,
I have a strange (at least for me) things happening. I am save the
result of an outer join into a file and I read this file and put these
values into a table.
The first row written contains the code page... how comes did I do
something wrong or is it a normal behavior?
I have no clue what "row contains the codepage" means in this context
Taking a wild guess you may want to consider the modifiers on EXPRT/LOAD
that suppress code-page conversion.
>I have to say that I did not manage to insert the value directly from
outer join....... I am not a DB2 Specialist.
INSERT INTO T SELECT * FROM S LEFT OUTER JOIN V WHERE S.C1 = V.C1
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Thanks Serge so let me explain...
I run this sql:
SELECT DISTINCT TSOUNAME, TSOPRGNAME FROM D1.TCEOQRYALLHPUGRP LEFT
OUTER JOIN
D1.TCEODATAUSTSO ON (TSOUNAME = QRYALLGRPHPUUS)
THEY ARE 2336 ROWS IN TCEODATAUSTSO AND 168332 ROWS IN
TCEOQRYALLHPUGRP SO I WANT TO SAVE THE RESULT INTO A NEW TABLE
TCEOHPUU (EXTRACT FROM A REXX) COL1 AND COL2 CONTAINS TSOUNAME
TSOPRGNAME
STATEMENT ="INSERT INTO D1.TCEOHPUU VALUES ('"COL1"','"COL2"')
INTERPRET SQQ "EXECUTE
IMMEDIATE :STATEMENT"
IF (SQLCA.SQLCODE <'0') & (SQLCA.SQLCODE <'-803')
THEN
CALL SQLCA "INPUTMETAHPU: INSERT STEP
FAILED"
INTERPRET FURZ
"COMMIT"
AND THAN I GET THIS RESULT
COL1 COL2
1208 1208 <-------------------- First row (when DB code page
UFT-8)
1252 1252 <-------------------- First row (when DB code
page Latin)
I LITTLE CLEARER NOW... ?
THANKS FOR YOUR HELP

Not a bit. But that may be because I don't know REXX.
I would look at the variable binding. For some reason you are extracting
the codepage instead of the values.
The issue very likely on the select (cursor) side and not the INSERT.
Now, why you are using a cursor with single row inserts it beyond me...

Cheers
Serge

PS: In general in the net using CAPS is considered shouting. This group
is fairy mellow w.r.t. netiquette, others are not....

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab


oK any serge Thanks for your help... I finally got it, I had first to
go over my code and try to simplify it...
This simply happened because when there were no rows to fetch (sqlcode
100) it put the codepage instead... any way it is fixed.

BTW could it be possible to put these two sql in one??
I mean conjugate the Select and inset in one statement... I am not a
SQL guru and this is a bit to advance for me... =:))

Jun 30 '07 #5

P: n/a
cb*****@gmail.com wrote:
BTW could it be possible to put these two sql in one??
I mean conjugate the Select and inset in one statement... I am not a
SQL guru and this is a bit to advance for me... =:))
INSERT INTO D1.TCEOHPUU
SELECT DISTINCT TSOUNAME, TSOPRGNAME
FROM D1.TCEOQRYALLHPUGRP LEFT OUTER JOIN D1.TCEODATAUSTSO
ON (TSOUNAME = QRYALLGRPHPUUS)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 30 '07 #6

P: n/a
On Jun 30, 2:33 pm, Serge Rielau <srie...@ca.ibm.comwrote:
cber...@gmail.com wrote:
BTW could it be possible to put these two sql in one??
I mean conjugate the Select and inset in one statement... I am not a
SQL guru and this is a bit to advance for me... =:))

INSERT INTO D1.TCEOHPUU
SELECT DISTINCT TSOUNAME, TSOPRGNAME
FROM D1.TCEOQRYALLHPUGRP LEFT OUTER JOIN D1.TCEODATAUSTSO
ON (TSOUNAME = QRYALLGRPHPUUS)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
thank you... I just got out from my laziness and try it too....
"INSERT INTO "SCHEM1".TCEOHPUU (USRT, SRDESC) ",
"SELECT DISTINCT TSOUNAME, TSOPRGNAME FROM ",
SCHEM1".TCEODATAUSTSO, "SCHEM1".TCEOQRYALLHPUGRP ",
"WHERE TSOUNAME = QRYALLGRPHPUUS"

Jun 30 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.