IBM Mainframe Forum

Hi All,

I am using a Right Outer join between 2 tables and selecting 3 fields from each, into a ps data set(D.S). The fields add upto 34 in length, i.e.,the data must end at 34th column in the D.S, but there are some extra columns inserted inbetween the fileds(of the Left Table) in the D.S. . .
Why is it happening:?: , is there any way to avoid this?

could you post your SQL and some sample output so that someone can have a look at it. You'll stand more of a chance at getting an answer.

Cheers
Steve

Hello,

Are there any varchar columns being selected?

Hi,
The query and output is given below..



O/P
the file is openned in browse mode along with COLS on

It is not clear where the ?s should be

Thanks steve for ur suggestion!

Thanks,
Gokul

Gokul

What position are the extra columns in. Is it the fullstops (or periods) in columns 8 and 19? giving you 2 extra bytes and making your record length 36 instrad of 34?

I'm not sure myself, so perhaps someone else may have a better idea than me, but can I ask how you are executing this and getting the output to a dataset. Are you using the DB2 unload utility DSNTIAUL?, if so, are the columns optional? If so then these could be the null indicator fields that will be unloaded. You may need to put a CHAR around the select of these fields so as to get a fixed length character string.

Cheers
Steve

Hello,

When posting source and data, you should use the "Code" tag for readability.

If you re-post the data with HEX On as well as COLS and use the Code tag it will help us help you.

those where periods comming immediatly after the each field...
hereafter i ll use code tag.. i dint no its usage.. thanks!!

OK, If I understand you, it is the T100.VEND_ID and the T100.VEND_CRT_DT fields where the problem is. And these fields have the period appearing after the field, and you are asking what these periods are doing in the file. Is this what you are asking? As mentioned above in my last post, are these fields OPTIONAL on the table, and how are you the getting the data into a dataset. Are you using the DSNTIAUL DB2 unload utility. Without this information I can't really help. As Dick mentions above, the data displayed with HEX ON will also help.
Cheers
Steve

Yes, that was my question,they are not optional fields in the table. I am using IKJEFT01 TO UNLOAD from the table. Cheking with HEX ON, i found that they were null charecters.

Gokul,
Well, I'm not really sure. You say you are using IKJEFT01 to unload, I take it you mean you are running a SPUFI in batch to do this? so you are executing the DSNTEP2 DB2 program? (look at your SYSTSIN parm to confirm this). Have you tried using DSNTIAUL DB2 unload utility to so this. If not I suggest you do and see what results you get with that. DSNTIAUL is also executed using IKJEFT01, but the SYSTSIN calls program DSNTIAUL rather then DSNTEP2.

DSNTEP2 Example
//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) -
LIBRARY('CSOFT.DSN1.RUNLIB.LOAD')
END

DSNTIAUL Example
//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL,200') -
LIB('CSOFT.DSN.RUNLIB.LOAD')

It's worth a try.

Steve.