As I am new to UDB a little help will be appreciated:-
1) We have got our test machine on a windows server and our prod machine is on an AIX server. How do I get the stored procedures written on the windows m/c on to the AIX m/c ( I think we will have to recompile them again on AIX anyway)

2) We are using DB2 V8.1 on AIX5.1. Is there anyway that we can tell DB2 the format of storing dates ? Or is it always stored as CCYY-MM-DD? I know that for DB2 on Mainframes we can specify the format of the date during installation.

3) Is there any function which converts a string to the DB2 expected date format if we supply the function with the format of the string ?

Regarding question #2. DB2 always stores dates in the same internal format using a 4 byte numeric field (2 numeric digits per byte). It is similar to OS/390 packed decimal without the sign in the last half byte. This is of little concern to you since you never see the date in DB2 internal format. Anytime a date column is returned to an application, or anytime you supply data to be inserted, it is always in character format of 10 bytes in length (which includes dash, slash, or period separators).

The string representation of date that you see is normally the default format of date/time values associated with the territory code of the application, unless overridden by specification of the DATETIME option when the program is precompiled or bound to the database. I believe that you can bind the CLI package with the desired DATETIME option also (or other package used to access DB2 dynamically).

In addition, the is can be overridden in any SQL statement with the following command:

SELECT CHAR(date-column, USA) FROM table-name

will return the date in USA format (MM/DD/YYYY) even if that is not the default. Other formats include ISO, EUR, JIS, or a site defined format. Check the SQL Reference Vol I for more information.

When inserting data, you can supply any of the above formats (YYYY-MM-DD, MM/DD/YYYY, etc) and DB2 will automatically recognize it properly as a date and store in the internal DB2 format. You do not have to identify the format to DB2 so long as it is one of the defined external formats for date, even if it is not the default for your installation.