Spooling SQL via sqlplus to flat file for Mainframe

Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

stek replied

18 May 2013, 18:26
Originally posted by doodab View Post

set linesize 372?

That will just set the line width doods, the output is the output - it's a size and if it's less than 372 yeah can be padded out. But it depends on a lot such as block size of the underlying AIX fs the report resides before it ends up on Mainframe, it's just not possible.

I've been told mainframe JCL etc requires jobs to have a certain size, hence the issue...
Leave a comment:
doodab replied

18 May 2013, 16:15
Originally posted by stek View Post

I got the output formatting from the Mainframe guy, he want's it to be exactly 372 bytes long! How's that gonna happen? He'll have to chunk it and/or pad it...

set linesize 372?
Leave a comment:
stek replied

16 May 2013, 13:05
Cheers fellas - it one one line with each record one the end of the previous, in vi Shift-A takes you to the end of the line and the file at the same time - wow!

I'll experiment later, got Tuxedo UBBCONFIG to deal with now. Gripping...

I got the output formatting from the Mainframe guy, he want's it to be exactly 372 bytes long! How's that gonna happen? He'll have to chunk it and/or pad it...
Leave a comment:
Spacecadet replied

16 May 2013, 12:15
Originally posted by stek View Post

Driving me bonkers!

Needs to be in format for Mainframe, fixed length fields, no spaces between, no newlines, no carriage returns, flat file, one long line.

Tried all the spool set commands under the sun, still get spaces and end of line, and don't want to resort to sed'ing or perl to sort it - surely sqlplus spooling can do it?

do it in the source SQL query

select
rpad(col1,length ' ') + rpad(col2, length,' ')

you can strip out any naughty characters at the same time
Leave a comment:
mudskipper replied

16 May 2013, 12:02
One line per record, or one line for everything?

Lots of SQLPlus options here sqlplus set command | SS64.com

Have you tried recsep and recsepchar ? It's not clear whether this suppresses the normal linebreak or is in addition.

Edit: SET RECSEP OFF might do the trick?

Last edited by mudskipper; 16 May 2013, 12:05.
Leave a comment:
stek started a topic Spooling SQL via sqlplus to flat file for Mainframe

16 May 2013, 11:41
Spooling SQL via sqlplus to flat file for Mainframe

Driving me bonkers!

Needs to be in format for Mainframe, fixed length fields, no spaces between, no newlines, no carriage returns, flat file, one long line.

Tried all the spool set commands under the sun, still get spaces and end of line, and don't want to resort to sed'ing or perl to sort it - surely sqlplus spooling can do it?
Tags: None