By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

db2 Script and Outputlinesize

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
> 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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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.

Nov 12 '05 #4

P: n/a
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.

Nov 12 '05 #5

P: n/a
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
Nov 12 '05 #6

P: n/a
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

Nov 12 '05 #7

P: n/a
Buck Nuggets wrote:

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

The oracle export utility is not an add-on cost
Nov 12 '05 #8

P: n/a
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

Nov 12 '05 #9

P: n/a
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
Nov 12 '05 #10

P: n/a
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)

Nov 12 '05 #11

P: n/a
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
Nov 12 '05 #12

P: n/a
*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

Nov 12 '05 #13

P: n/a
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
Nov 12 '05 #14

P: n/a
Munich .... Oktoberfest ...

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

Kind Regards

Michael

Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.