db2 Script and Outputlinesize

[michael.bierenfeld]

Hi, there .....

coming from the Oracle World I am wondering if there is a DB2 command
like "set linesize 1000" in SQL*Plus.

The DB2 Commandline inserts <CR> after n Characters but I want all
characters from a SELECT Row in one line.

How do I do that with DB2.

Sorry for my bad english.

Kind regards

Michael

[Buck Nuggets]

> coming from the Oracle World I am wondering if there is a DB2 command

like "set linesize 1000" in SQL*Plus.

oracle has a lot of built-in functionality for creating primitive
reports from sql. I suspect that today this is mostly used for
exporting data to files (since the oracle export utility is an add-on
cost).

db2 doesn't have much functionality for creating reports directly out
of sql - but it has a free export utility. So, it really doesn't need
this functionality much.

buck

[michael.bierenfeld]

Correct.

I am not exporting any data. i have a simple script that reads some
data for monitoring the db2 instance.

the output of the script is displayed on a web page (NAGIOS) to show
the status of the database.

script is :

SELECT CASE(HI_ID)
WHEN 1001 THEN 'state of the database'
WHEN 1002 THEN 'shared sorting mem util'
WHEN 1003 THEN 'percent sort overflow'
WHEN 1004 THEN 'max sort mem used'
WHEN 1005 THEN 'fullness of transaction log'
WHEN 1006 THEN 'fullness of log FS'
WHEN 1007 THEN 'rate of deadlock'
WHEN 1008 THEN 'locklist utilization'
WHEN 1009 THEN 'DB lock esc. rate'
WHEN 1010 THEN 'percent apps waiting on locks'
WHEN 1011 THEN 'package cache hitratio'
WHEN 1012 THEN 'catalog cache hitratio'
WHEN 1013 THEN 'shared SQL workspace hitratio'
WHEN 1014 THEN 'heap utilization'
WHEN 1015 THEN 'manual reorg required'
WHEN 1016 THEN 'hadr op status'
WHEN 1017 THEN 'hadr delay'
WHEN 1018 THEN 'manual backup required'
WHEN 1022 THEN 'runstats'
ELSE CHAR(HI_ID)
END as "AREA OF ISSUE",
HI_ALERT_STATE_DETAIL AS STATUS,
HI_VALUE, substr(HI_ADDITIONAL_INFO,1,100) as HI_ADDITIONAL_INFO
FROM DB2$HEALTH_DB_HI
WHERE HI_ALERT_STATE != 1

and the view DB2$HEALTH_DB_HI is defined as

create view DB2$HEALTH_DB_HI as select * from
table(health_db_hi('',-1)) as sntable;

The work has been done by a guy named eaton. I am just in the process
to build a simple naggios plugin to monitor db2

kind regards

Michael

[Ian]

mi****************@web.de wrote:

Hi, there .....

coming from the Oracle World I am wondering if there is a DB2 command
like "set linesize 1000" in SQL*Plus.

The DB2 Commandline inserts <CR> after n Characters but I want all
characters from a SELECT Row in one line.

The DB2 CLP does *not* insert line breaks. If you are seeing data that
appears to be on multiple lines that is your shell wrapping the long
lines.

i.e. db2 -x "select really,long,column,list from table" > x.out

should have N number of lines, where N = number of rows in table.

[michael.bierenfeld]

it *does* at least this one :-)

db2inst1@db2host:/home/db2inst1 # db2 -tf /nagios/bin/health_check.sql

/var/tmp/lala

db2inst1@db2host:/home/db2inst1 # wc /var/tmp/lala
7 13 353 /var/tmp/lala
db2inst1@db2host:/home/db2inst1 # cat /var/tmp/lala

AREA OF ISSUE STATUS HI_VALUE
HI_ADDITIONAL_INFO
----------------------------- -------------------- --------
----------------------------------------------------------------------------------------------------

0 Satz/Sätze ausgewählt.

[Norbert Munkel]

Hi Michael,

if you investigate the result file you might find that there is one
empty row at the start and 3 empty rows at the end of the file.

Even the switch "-x" adds one empty row at the end.

regards,

Norbert

[michael.bierenfeld]

Yes ! And then ... ?-)

-x means suppress column headers. It does not seem to be so simple.I
think I gonna write a small perl/python script

[Mark Townsend]

Buck Nuggets wrote:

since the oracle export utility is an add-on cost.

The oracle export utility is not an add-on cost

[Norbert Munkel]

Michael,

mi****************@web.de schrieb:

Yes ! And then ... ?-)

-x means suppress column headers. It does not seem to be so simple.I
think I gonna write a small perl/python script

This was just to show you that this is no option as well and db2 is
_NOT_ wrapping lines. Sorry for the confusion.

For nagios, I would write a wrapper around that which gives proper
return-codes and just one short line of (HTML) text linking to the
"long"-Output.

regards,

Norbert

[Knut Stolze]

mi****************@web.de wrote:

it *does* at least this one :-)

db2inst1@db2host:/home/db2inst1 # db2 -tf /nagios/bin/health_check.sql

/var/tmp/lala db2inst1@db2host:/home/db2inst1 # wc /var/tmp/lala
7 13 353 /var/tmp/lala
db2inst1@db2host:/home/db2inst1 # cat /var/tmp/lala

AREA OF ISSUE STATUS HI_VALUE
HI_ADDITIONAL_INFO
----------------------------- -------------------- --------

----------------------------------------------------------------------------------------------------
0 Satz/Sätze ausgewählt.

Have a look at /var/tmp/lala using "ls -S" as it will preserve the line
wraps exactly as they are in the file. You'll see that DB2 does not
include any <cr>s.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[michael.bierenfeld]

Hi there,

_it_ _does_ .... The following pipe Script recognizes them and filters
them out. The Problem is solved for me. I will post the
"nagios-pluggin" to the nagios projekt

#!/usr/bin/env python
# -*- coding: latin_1 -*-
# $Id:$

import os
import sys
import string

# ---------------------------------------------------------------------

def wrapit (data, stdout):

wrapped = data.replace ("\n", " ").split ("XXXX")
for line in wrapped:
stdout.write (line)

return

# ---------------------------------------------------------------------

if __name__ == "__main__":

stdin = sys.stdin
stdout = sys.stdout

data = stdin.read ()

wrapit (data, stdout)

[Knut Stolze]

mi****************@web.de wrote:

Hi there,

_it_ _does_ .... The following pipe Script recognizes them and filters
them out. The Problem is solved for me. I will post the
"nagios-pluggin" to the nagios projekt

Seriously, DB2 does not include any line breaks in the middle of the rows
(only at the end of each row, of course). So you are addressing symptoms
if your output or changes that come from another place.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[michael.bierenfeld]

*hm* :-) we must have some critters inserting <cr>'s. I am talking
about

db2inst1@db2host:/home/db2inst1 # db2
(c) Copyright IBM Corporation 1993,2002
Befehlszeilenprozessor für DB2 SDK 8.2.3

Running on AIX 5.2

Regards

Michael

[Knut Stolze]

mi****************@web.de wrote:

*hm* :-) we must have some critters inserting <cr>'s. I am talking
about

db2inst1@db2host:/home/db2inst1 # db2
(c) Copyright IBM Corporation 1993,2002
Befehlszeilenprozessor für DB2 SDK 8.2.3

Running on AIX 5.2

Run

db2 -x "<query>" > <file>

and have a look at <file> with a hex-editor.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[michael.bierenfeld]

Munich .... Oktoberfest ...

Working the day after a couple of Wiesn Maß is not the best idea

Kind Regards

Michael