Hi I'm a DB2 newb, company IT dept decided to upgrade to DB2 from Oracle on system I work on thus a lot of stuff I created have to be converted as fast as I can work on it, been reading on the SQL cookbook and a lot of online stuff and bought SQL99 bible but I still have a few quick questions on stuff that I need to convert from Oracle to DB2:

whats the DB2 equivalent of:

1. SET PAGESIZE,LINESIZE,FEEDBACK,VERIFY,UNDERLINE

or any other formatting function in oracle.

2. Is there a FirstDay, Lastday function in DB2, if I want to get the first day of this month and the last day of this month in date format what would I use?

3. Is there any other function to spool in DB2 that instead of appending to the output file it overwrites it? The best way I know right now is delete the output file before running the query that produces it again.

Some more info. I can only use DB2 connect to connect to our db2 servers and I usually create script files that produces outputs in csv, txt, etc. etc. format. I've been working with Oracle for years and am just jumping in to DB2 so other than the usual SQL I'm not familiar yet with a lot of DB2 stuff.

Advance Thanks for any help.

Hi db2newb,

in DB2, the pagesize is being determined when you create a tablespace, default is 4 KB.

I can't make much sense out of LINESIZE, but if it is the maximum size a record can have in Oracle, there is no corresponding parameter in DB2. The largest row size in DB2 just needs to fit into a page. Exceptions may apply to LOB types, but anyway I don't know how DB2 stores them internally.

About FEEDBACK,VERIFY,UNDERLINE I don't know anything.

You can derive this month's first day using this expression:

values ( current date - ( day( current date ) - 1 ) days )

And the last day of this month is:

values ( current date - ( day( current date) ) days + 1 months )

You can define UDFs to emulate date functions you had in Oracle. In DB2 formatting data types for output is considered a client application business. But I believe you can play around with client language settings too. Or use the functions timestamp_format or timestamp_iso.

Can you tell us what you mean by spooling?

Johann

Johann: PAGESIZE and LINESIZE are some of the parameters that control screen output of SQLplus, Oracle's command line processor.

There are no equivalents in DB2 CLP - you can't control output format (except maybe just removing the header lines...).

newb: You could "spool" your output while overwriting previous results by OS output redirection, like:

db2 +x -tf yourscript.sql > out.put

In general, DB2 CLP is not very suitable for formatting your output. You could achieve better results by formatting query result in SQL, e.g. using SUBSTR() and CONCAT() functions to control output line length etc.

OK I see, thank you. I guess DB2 CLP isn't really like SQL plus. I wish I had other tools that I can use but oh well have to make the most of whats available today.

Thanks again.

Most DB2 shops have 3rd party query tools. The price of DB2 compared to Oracle reflects the fact that some extra products may need to be purchased for DB2.

Good point, I'm actually looking into Embarcadero Rapid SQL. Looks like this will work out for me.
Wish me luck on my DB2 transition....