INSERT Trouble

[cberthu]

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

[Serge Rielau]

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

[cberthu]

On Jun 21, 1:10 pm, Serge Rielau <[e-mail]:

[e-mail] 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

[Serge Rielau]

cb*****@gmail.com wrote:

On Jun 21, 1:10 pm, Serge Rielau <[e-mail]:

>[e-mail] 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

[cberthu]

On Jun 22, 1:24 pm, Serge Rielau <[e-mail]:

[e-mail] wrote:

On Jun 21, 1:10 pm, Serge Rielau <[e-mail]:

[e-mail] 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... =:))

[Serge Rielau]

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

[cberthu]

On Jun 30, 2:33 pm, Serge Rielau <[e-mail]:

[e-mail] 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"