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

*urgent* [LOAD] UDB v 7.1 OS/390 DATE problem

P: n/a
Hi NG,

we've got a very urgent problem... :(

We are importing data with the LOAD utility. The input DATE field data
is in the format DDMMYYYY (for days) and MMYYYY (for months).
The target data format is european DD.MM.YYYY (for days) and
01.MM.YYYY (for months).

The input format is not recognizable as a DATE input to a DB2 LOAD for
UDB v 7.1 OS/390. :((

Unfortunately we don't have any tools to rebuild the input. :(((

How can this problem be solved?!

It is very urgent...

Appreciate everyone's help!!!!!!

S.B.
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"Stefan Bauer" <st***********@yahoo.de> wrote in message
news:64**************************@posting.google.c om...
Hi NG,

we've got a very urgent problem... :(

We are importing data with the LOAD utility. The input DATE field data
is in the format DDMMYYYY (for days) and MMYYYY (for months).
The target data format is european DD.MM.YYYY (for days) and
01.MM.YYYY (for months).

The input format is not recognizable as a DATE input to a DB2 LOAD for
UDB v 7.1 OS/390. :((

Unfortunately we don't have any tools to rebuild the input. :(((

How can this problem be solved?!

It is very urgent...

Appreciate everyone's help!!!!!!

S.B.


Dates can be in any of the following formats. You can omit leading zeros for
month and day. Trailing blanks can be included, but no leading blanks are
allowed.
- dd.mm.yyyy
- mm/dd/yyyy
- yyyy-mm-dd
- any local format defined by your site at the time DB2 was installed.

Looks like you maybe out of luck. You don't have any programmers who can put
the dashes/slashes/periods in the date?
Nov 12 '05 #2

P: n/a
Hi Stefan,

Mark A wrote:
"Stefan Bauer" <st***********@yahoo.de> wrote in message
news:64**************************@posting.google.c om...
Hi NG,

we've got a very urgent problem... :(

We are importing data with the LOAD utility. The input DATE field data
is in the format DDMMYYYY (for days) and MMYYYY (for months).
The target data format is european DD.MM.YYYY (for days) and
01.MM.YYYY (for months).

The input format is not recognizable as a DATE input to a DB2 LOAD for
UDB v 7.1 OS/390. :((

Unfortunately we don't have any tools to rebuild the input. :(((

How can this problem be solved?!

It is very urgent...

Appreciate everyone's help!!!!!!

S.B.


Dates can be in any of the following formats. You can omit leading zeros
for month and day. Trailing blanks can be included, but no leading blanks
are allowed.
- dd.mm.yyyy
- mm/dd/yyyy
- yyyy-mm-dd
- any local format defined by your site at the time DB2 was installed.

Looks like you maybe out of luck. You don't have any programmers who can
put the dashes/slashes/periods in the date?


I don't know DB2/390's load utility but on Unix/Windows you would really
have to transform the data. How much data is it?
If you don't have a tool on OS/390 to reformat text (then throw that OS away
;-)) you might be able to transfer it to a unix box?
Give me/us some lines of your data and I/we may be able to provide you with
a script to transform the data.
If you cannot use ftp or some host terminal session for the transfer, you
could even load the data as CLOB into a table and read that from a unix
client.

These are quite desperate measures - but you said it's urgent...

HTH

Joachim Banzhaf
Nov 12 '05 #3

P: n/a
"Joachim Banzhaf" <jb******@ngi.de> wrote in message
news:d5************@banzhaf.dnsalias.net...
Hi Stefan,

I don't know DB2/390's load utility but on Unix/Windows you would really
have to transform the data. How much data is it?
If you don't have a tool on OS/390 to reformat text (then throw that OS away ;-)) you might be able to transfer it to a unix box?
Give me/us some lines of your data and I/we may be able to provide you with a script to transform the data.
If you cannot use ftp or some host terminal session for the transfer, you
could even load the data as CLOB into a table and read that from a unix
client.

These are quite desperate measures - but you said it's urgent...

HTH

Joachim Banzhaf

There are plenty of tools/programming languages on OS/390 to change the
data, but apparently the DBA's are not allowed to change the data, and maybe
this is not a one time effort (the data will be exported and loaded in an
automated repeatable process).
Nov 12 '05 #4

P: n/a
Mark A wrote:
"Joachim Banzhaf" <jb******@ngi.de> wrote in message
news:d5************@banzhaf.dnsalias.net...
Hi Stefan,

I don't know DB2/390's load utility but on Unix/Windows you would really
have to transform the data. How much data is it?
If you don't have a tool on OS/390 to reformat text (then throw that OS away
;-)) you might be able to transfer it to a unix box?
Give me/us some lines of your data and I/we may be able to provide you

with
a script to transform the data.
If you cannot use ftp or some host terminal session for the transfer, you
could even load the data as CLOB into a table and read that from a unix
client.

These are quite desperate measures - but you said it's urgent...

HTH

Joachim Banzhaf

There are plenty of tools/programming languages on OS/390 to change the
data,


I know, thats why there was a smiley.
but apparently the DBA's are not allowed to change the data, and
maybe this is not a one time effort (the data will be exported and loaded
in an automated repeatable process).


That does not change anything. There are two options:
1)
get the permission to change the data
get someone who knows how to change the data on os/390
2)
transfer the data to a box where permission is granted
(e.g. local windows/linux box)
modify the data
import or retransfer and load the modified data

Sure enough I would prefer solution 1), but if that is not possible...
Both solutions can be automated (I have done it for 2)).

Joachim Banzhaf

Nov 12 '05 #5

P: n/a
> > but apparently the DBA's are not allowed to change the data, and
maybe this is not a one time effort (the data will be exported and loaded in an automated repeatable process).


That does not change anything. There are two options:
1)
get the permission to change the data
get someone who knows how to change the data on os/390
2)
transfer the data to a box where permission is granted
(e.g. local windows/linux box)
modify the data
import or retransfer and load the modified data

Sure enough I would prefer solution 1), but if that is not possible...
Both solutions can be automated (I have done it for 2)).

Joachim Banzhaf

If the extract and load is part of a repeatable process in a production
environment, then the proper way to fix it is to get the people who created
the extract to fix the data so it is in a form that can be loaded into DB2
on a regular basis in an automated scheduled production job.

Getting the people who created the extract is possible, but it apparently
takes a change request and some time to do it.

There is no need to transfer the data to a windows/linux box. There are
plenty of tools on the OS/390 box to make the changes if they wanted to do
that (apparently they don't for the reasons described above).
Nov 12 '05 #6

P: n/a
Thank you very much for all replies!
If the extract and load is part of a repeatable process in a production
environment, then the proper way to fix it is to get the people who
created the extract to fix the data so it is in a form that can be loaded
into DB2 on a regular basis in an automated scheduled production job.


Yes, it is. The load will run monthly and completely automatic. So we sent a
change request to the export team. At the same time we decided to migrate
the database to UDB v 8.1 on Linux for the production phase, which was also
available, but wasn't the development plattform for our DB. AFAIK, then
we'll be also able to use modifiers in the LOAD utility, like
timeformat="MMYYYY" and timeformat="DDMMYYYY". Furthermore is Linux our
favorite OS. :-)

But I'm not sure, that everything will be done till Monday. And our deadline
is close. So, it would be very cool, if we could get little help from you
in scripting the tool, what would modify the data. :)

We would appreciate every help!!!

S.B.

Mark A wrote:
> but apparently the DBA's are not allowed to change the data, and
> maybe this is not a one time effort (the data will be exported and loaded > in an automated repeatable process).


That does not change anything. There are two options:
1)
get the permission to change the data
get someone who knows how to change the data on os/390
2)
transfer the data to a box where permission is granted
(e.g. local windows/linux box)
modify the data
import or retransfer and load the modified data

Sure enough I would prefer solution 1), but if that is not possible...
Both solutions can be automated (I have done it for 2)).

Joachim Banzhaf

If the extract and load is part of a repeatable process in a production
environment, then the proper way to fix it is to get the people who
created the extract to fix the data so it is in a form that can be loaded
into DB2 on a regular basis in an automated scheduled production job.

Getting the people who created the extract is possible, but it apparently
takes a change request and some time to do it.

There is no need to transfer the data to a windows/linux box. There are
plenty of tools on the OS/390 box to make the changes if they wanted to do
that (apparently they don't for the reasons described above).


Nov 12 '05 #7

P: n/a
Mark A wrote:
If the extract and load is part of a repeatable process in a production
environment, then the proper way to fix it is to get the people who
created the extract to fix the data so it is in a form that can be loaded
into DB2 on a regular basis in an automated scheduled production job.
Completly agree.
Getting the people who created the extract is possible, but it apparently
takes a change request and some time to do it.
And that translates - given the subject *urgent* - to NOT possible.
There is no need to transfer the data to a windows/linux box. There are
plenty of tools on the OS/390 box to make the changes if they wanted to do
that (apparently they don't for the reasons described above).


Solution 2) is inelegant and inefficient but it IS possible.
If it is really urgent, I'd use it as a temporary fix until there is time to
do it right.

Joachim Banzhaf

Nov 12 '05 #8

P: n/a
Stefan Bauer wrote:
Thank you very much for all replies!
You are welcome.
AFAIK, then we'll be also able to use modifiers in the LOAD utility, like
timeformat="MMYYYY" and timeformat="DDMMYYYY". Furthermore is Linux our
Correct
favorite OS. :-)
Mine too - you guessed it? But I don't think there is such a thing as a
"better" OS without considering the task to accomplish.
But I'm not sure, that everything will be done till Monday. And our
deadline is close. So, it would be very cool, if we could get little help
from you in scripting the tool, what would modify the data. :)

We would appreciate every help!!!

S.B.


You mean something like this? (hope you get it in one piece)

$ echo 'hi 123456 12345678 234567 23456789 stefan' | sed -e
's/\([^0-9]\)\([0-9][0-9]\)\([0-9][0-9][0-9][0-9]\)\([^0-9]\)/\1\3-\2-01\4/g'
-e
's/\([^0-9]\)\([0-9][0-9]\)\([0-9][0-9]\)\([0-9][0-9][0-9][0-9]\)\([^0-9]\)/\1\4-\3-\2\5/g'
hi 3456-12-01 5678-34-12 4567-23-01 6789-45-23 stefan

Good luck,

Joachim Banzhaf

Nov 12 '05 #9

P: n/a
Hi Stefan,
for future references, whenever it's not possible to change the content of
the input dataset, you can load it on a copy of the target table that has as
many additional character columns as the number of fields that are causing
the problem, which in your case is 2. In other words, if the table you are
trying to load has, let's say, 4 columns named COLA, COLB, COLC, and COLD,
you can create a copy of this table adding two more columns at the end. When
loading the copy, you should make sure that the troubled fields are loaded
into the newly added fields instead of into their original columns. That can
easily be done by manipulating the SYSPUNCH for the load utility. Once the
table is loaded, you can populate the original columns using an update
statement. For example, if the DATE format in use at your shop is
YYYY-MM-DD and the charcter field you have just loaded contains a date in a
DDMMYYYY format, the update would be:

UPDATE <table name> SET <target column> =
SUBSTR(<column>,5)||'-'||SUBSTR(<column>,3,2)||'-'||SUBSTR(<column>,1,2)

Once you're done with this, you can then load the original table using a
cursor that points to the copy you created previously.
Mauro.
"Stefan Bauer" <st***********@yahoo.de> wrote in message
news:64**************************@posting.google.c om...
Hi NG,

we've got a very urgent problem... :(

We are importing data with the LOAD utility. The input DATE field data
is in the format DDMMYYYY (for days) and MMYYYY (for months).
The target data format is european DD.MM.YYYY (for days) and
01.MM.YYYY (for months).

The input format is not recognizable as a DATE input to a DB2 LOAD for
UDB v 7.1 OS/390. :((

Unfortunately we don't have any tools to rebuild the input. :(((

How can this problem be solved?!

It is very urgent...

Appreciate everyone's help!!!!!!

S.B.

Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.