the SQL utility just outputs to stdout, and lets shell (QShell) make the
file, so I don't know any reason why it would work differently than any
For NetServer... in i Nav, go under YourServer/File Systems/File
Shares. Right-click on the share that you're using and choose
properties. Click the "Text Conversion" tab. This is how you configure
whether i Nav does conversion or not, and how it does so. You can
control which files get translated by their extension, too... I must
admit, I'm no expert here, as I don't use this conversion at all.
I don't think of telling QShell to translate is a "workaround", in fact,
IMHO that's where the conversion _should_ take place. Having NetServer
do it is the workaround, IMHO... but, to each his own. I don't want to
disagree with you too strongly, I might miss out on some ribs :-)
As for the other questions you asked:
1) It's been 5 years since I wrote this, I don't remember what the
leading LF is for... I think it has something to do with the way the
titles interact with the -a/-b options, though. If you add the -q
(lowercase Q -- there is also an uppercase Q, but that does something
different) it should suppress this.
2) The -c options controls what is printed between characters, but there
is no "special value" for tab, you need to feed in the tab character.
an easy way to do this is with the printf utility, like this:
sql -SQq -c "$(printf '\t')" "select blah from mytable"
the $() will run another QShell utility and substitute it's output into
the command string, so I'm running printf to print a tab (\t) and
inserting it as the value for the -c option.
3) Ahhh.. well, this utility was designed to use the Unix end-of-line
character, since it runs in QShell (not the Windows CRLF). But, it does
have an option -a that can be used to print something at the end of each
record, so you could have it print the CR character at the end. You'd
have to use printf to get the CR character (much like you did for the
tab) so it'd be like this:
sql -SQq -c "$(printf '\t')" -a "$(printf '\r')" "select blah from mytable"
Sorry, that's uglier than I really wanted it to be. You can see that I
made it quite versatile, but inserting hex characters is a bit ugly. :-(
On 3/5/2014 3:00 PM, Charles Wilt wrote:
Yeah, I can work-around it easily enough...just hoping to understand what's
Netserver is configured as CCSID 1252
I don't see anyplace off in a quick look to specify what gets translated
wand what doesn't.
But as I mentioned, it works on other .TXT files (and .TAB for that matter.)
While I've got your attention... :)
Is there a way in the SQL command,
1) to remove the leading LF, x'25' that gets put into the file
2) to specify the TAB char as the separator
3) to use a CRLF as a record separator
Thanks! (I'm going to owe you dinner again)
On Wed, Mar 5, 2014 at 3:44 PM, Scott Klement
If you want, QShell can write the data to the file in ASCII. Just
pre-create your 'hhc_custs.txt' file in a different CCSID like this:
touch -C 819 /home/wiltc/hhc_custs.txt
Then when you output to the file, it should be translated to CCSID 819
(which is ISO-8859-1, a flavor of ASCII).
As for why NetServer would translate some files but not others... I'd
need to see how your NetServer is configured. In it's default
configuration, NetServer does no translation at all. But, you can
configure it to translate certain file types, et al, in the Netserver
settings that are found in i Nav.
Personally, I always prefer translation to be turned off in NetServer.
I prefer to do my translation in my programs (or by hand). The vast
majority of files I have in the IFS should not be translated, so I would
rather it not do so automatically.
On 3/5/2014 2:24 PM, Charles Wilt wrote:
Playing around with Scott's "Improved DB2 Command for QShell" aka SQLQSH
Ran the following:
sql -S -c "|" "select ccusnr, ccusnm from olsdta.corcustm" >
An I get nice pipe delimited stream file, when viewed via the green
However, even I open it using my favorite text editor, Ultra Edit (UE)
Windows explorer, I get "garbage". Or at least untranslated EBCDIC. :)
>From DSPF, for the first line I see
C3C3E4E2 D5D94FC3 C3E4E2D5 D425
>From UE on windows I see the same hex values.
Now, I've got other text stream files in the same folder also coded as
CCSID 37 that open just fine in UE. So it doesn't appear to be a general
issue with Netserver.
If I FTP the file back to my PC, it get's translated properly. So the
issue appears to be just with Netserver and just with this file.
Does anyone have any thoughts as to why Netserver isn't converting this
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives