OUTPUT ... and File Layout
Dirk Moolman's OUTPUT TO statement generating update statistics commands split long lines in two, causing syntax errors. John Carlson said OUTPUT wraps at 80 characters and switched to UNLOAD TO, leaving only pipe delimiters to clean up. Jonathan Leffler suggested UNLOAD with DELIMITER ';' and omitting the semicolon from the string, or using SQLCMD.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I am generating a script using an unload - actually an output statement:
output to med_h.sql without headings
select unique "update statistics high for table "||trim(tabname)||"("||trim(colname)||") distributions only;"
from systables a, syscolumns b, sysindexes c
where a.tabid = b.tabid
and b.tabid = c.tabid
and b.colno = c.part1
and a.tabid > 99
and tabtype = "T"
The problem is that, the lines are too long and get cut in half, for example
update statistics high for table zzz_cont_contrib(serial_no) dist
ributions only;(2 separate lines)
and this gives me syntax errors. Is there an easy way to generate these lines so that they are actually one line inside the output file ?
Dirk Moolman
Database and Unix Administrator
MXGROUP
I feel like I'm diagonally parked in a parallel universe
On Thu, 22 May 2003 14:27:24 +0200, "Dirk Moolman"
<DirkM@mxgroup.co.za> wrote:
OUTPUT seems to dump data so that the column width is no more than 80
characters. I was able to change OUTPUT TO to UNLOAD TO. Only
cleanup required would be to cleanup the "pipe" delimiters.
>
>I am generating a script using an unload - actually an output statement:
>
>output to med_h.sql without headings
>select unique "update statistics high for table "||trim(tabname)||"("||trim(coln>ame)||") distributions only;"
>from systables a, syscolumns b, sysindexes c
>where a.tabid = b.tabid
>and b.tabid = c.tabid
>and b.colno = c.part1
>and a.tabid > 99
>and tabtype = "T"
>
>
>
>The problem is that, the lines are too long and get cut in half, for example
>
>update statistics high for table zzz_cont_contrib(serial_no) dist
> ributions only;>(2 separate lines)
>
>
>
>and this gives me syntax errors. Is there an easy way to generate these lines so that they are actually one line inside the output file ?
>
>
>
>
>
>
>
>
>Dirk Moolman
>Database and Unix Administrator
>MXGROUP
>
>
>I feel like I'm diagonally parked in a parallel universe
>
John Carlson wrote:
> On Thu, 22 May 2003 14:27:24 +0200, "Dirk Moolman"
> <DirkM@mxgroup.co.za> wrote:
>
> OUTPUT seems to dump data so that the column width is no more than 80
> characters. I was able to change OUTPUT TO to UNLOAD TO. Only
> cleanup required would be to cleanup the "pipe" delimiters.
UNLOAD TO FILE DELIMITER ';' SELECT ...
and omit the semi-colon from the string literal.
No sed needed.
Alternatively, try SQLCMD -- it behaves itself the way you want
anyway. And you could do:
output '/tmp/my.file';
delim ';';
select ....;
output '/dev/stdout';
input '/tmp/my.file';
!rm /tmp/my.file
>>I am generating a script using an unload - actually an output statement:
>>
>>output to med_h.sql without headings
>>select unique "update statistics high for table "||trim(tabname)||"("||trim(coln>>ame)||") distributions only;"
>
>>from systables a, syscolumns b, sysindexes c
>
>>where a.tabid = b.tabid
>>and b.tabid = c.tabid
>>and b.colno = c.part1
>>and a.tabid > 99
>>and tabtype = "T"
>>
>>
>>
>>The problem is that, the lines are too long and get cut in half, for example
>>
>>update statistics high for table zzz_cont_contrib(serial_no) dist
>> ributions only;>>(2 separate lines)
>>
>>
>>
>>and this gives me syntax errors. Is there an easy way to generate these lines so that they are actually one line inside the output file ?
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
On Thu, 22 May 2003 15:59:24 GMT, Jonathan Leffler
<jleffler@earthlink.net> wrote:
>John Carlson wrote:
>> On Thu, 22 May 2003 14:27:24 +0200, "Dirk Moolman"
>> <DirkM@mxgroup.co.za> wrote:
>>
>> OUTPUT seems to dump data so that the column width is no more than 80
>> characters. I was able to change OUTPUT TO to UNLOAD TO. Only
>> cleanup required would be to cleanup the "pipe" delimiters.
>
>
>UNLOAD TO FILE DELIMITER ';' SELECT ...>
>and omit the semi-colon from the string literal.
>
>No sed needed.
>
>Alternatively, try SQLCMD -- it behaves itself the way you want
>anyway. And you could do:
>
I need to try it someday . . . . .