Please contact us at support@koretech.com if you would like to share one of your Tech Tips or need help solving a technical challenge. We would be happy to help you.

Exporting Date-Time Stamps Using Kourier Integrator

A common requirement when exporting data to a MS SQL Server database is to stamp the exported row (record) with a date-time stamp. DBAs will often use this information when they analyze the data. Another similar requirement is to export an existing date and time fields from a UniData/UniVerse record to a single datetime column in a SQL Server database. This tech tip will show you how to setup your Export Specifications to use date-time stamps and what you need to do to export existing date and time stamps fields that exist in your UniData/UniVerse Database. These techniques will work using either Kourier Integrator for U2 or Kourier Integrator Classic.

Adding a Date-Time Stamp to Exported Data

We'll start with the simplest example, adding a date-time stamp to an exported row. Note, this tip was first published in our Summer 2006 newsletter in an "Ask Kore" article. However, it's a very common question and for completeness it's being included here.

In order to automatically stamp each exported record with a date and time to indicate when it was loaded into your data warehouse, all you need to do is create a new line in the Export Specification for Field ID "0" and then enter this in the Conversion field, including quotes:

"{@DATETIME}"

Note: This must be uppercase, and don't forget to create a SQL datetime type column in your SQL database before running the export.

See the example Kourier Integrator for U2 Export Specifications screen below for how you could enter this. Best practice is to add the date-time stamps as the last exported field, but it can be anywhere actually.

That's all there is to it.

Exporting Existing U2 date and Time Stamps

If your data record has an existing date stamp and a time stamp which indicates the moment on which the U2 record was updated or created, you may want to store this in your SQL database as a single datetime value, rather than two separate datetime values (one for the date, one for the time). While Kourier Integrator does not currently have a way to create this kind of data field, it can easily be done using an I-descriptor, or "virtual" attribute". There are a number of ways to do this, depending on how the data is stored in your U2 application. The methods described here can be used in either Kourier Integrator for U2 or Kourier Integrator Classic (for DataFlo.)

Example 1

For our first example, let's take the KT_LOG file, which is a file that is part of the Kourier Integrator system. KT_LOG stores the event date in Attribute 1 and the event time in Attribute 2. How would you get this to export to SQL as a single datetime value?

The answer is to create an I-descriptor dictionary item which displays the data correctly. SQL wants to see a datetime value in the format "mm/dd/yyyy hh:mm:ss". So, let's create an I-descriptor called "EVENT_DATETIME" in the Dictionary of KT_LOG. In DataFlo, you would use the standard EDIT.DICT screen. In Kourier Integrator for U2, you would use the native U2 editor. Our formula (in Attribute 2 of the dictionary item) would look like this:

OCONV(EXTRACT(@RECORD,1,0,0),'D4/'):" ":OCONV(EXTRACT(@RECORD,2,0,0),'MTS')

The length of our display field would be 19 characters (10 characters for the date, 1 character for the space, and 8 characters for the time). So, the entire Dictionary record would look like this:

001: I
002: OCONV(EXTRACT(@RECORD,1,0,0),'D4/'):" ":OCONV(EXTRACT(@RECORD,2,0,0),'MTS')
003:
004: DateTime Stamp
005: 19L
006: S

If you have D-Type dictionaries (as in DataFlo), you can simplify the formula as follows:

OCONV(F1,'D4/'):" ":OCONV(F2,'MTS')

Using the Dictionary Editor (EDIT.DICT) screen in DataFlo for illustration purposes, it would look like this:

Once you have successfully created the new dictionary, in this case called EVENT_DATETIME, all you have to do is add it to your Kourier Integrator Export Specification. No other Conversion or Format code is required. Make sure you have create a SQL datetime type column in your SQL database before using the export.

The advantage of the first formula is that it will work on any U2 system, and is not dependent on any other dictionaries. In the rest of this tech tip, we will be using the more complex style, as it will work anywhere. See the other examples below are more complex examples of how to export date and time stamps.

Example 2

OK, so that's fine for a simple case where the date and time are in separate attributes. How about another common scenario, where there is a single stamp attribute which has the User ID, date and time separated by asterisks or some other character? For example, let's say that Attribute 73 of your data file has this structure, and a sample record has the data "JSEGAL*14641*52437". How would we get this as a datetime stamp? Well, here is the formula we would use:

OCONV(OCONV(EXTRACT(@RECORD,73,0,0),'G1*1'),'D4/'):" ":OCONV(OCONV(EXTRACT(@RECORD,73,0,0),'G2*1'),'MTS')

It's pretty much the same, except that we are using the "G" Group Extract conversion to separate out the 2nd and 3rd parts of the "*" delimited field. Using this dictionary on the record with the sample data above would give us the output "01/31/2008 14:33:57".

Example 3

Now for the really complex example. What about when you have MultiValued line items being exported to a child table in SQL, and you have date and time stamps as part of the line item information? Well, the OCONV function (and many other functions) will not work properly on MultiValued data. Similarly, the ":" operator for concatenation will not work on MultiValued data. Instead, we have to use the SUBR (Subroutine) functions provided in both UniData and Universe for this situation. It does mean that our formula will get longer and less readable. But we can still make it work!

Let's say that we have associated MultiValues in attributes 41 through 79 of your U2 data file, and associated Attributes 52 and 53 have the date and time, respectively. We can still use the EXTRACT function to get all of the MultiValues in each attribute, but we have to use the SUBR("-OCONVS",data,conversion) function and the SUBR("-CATS",value1,value2) function to properly format the data. Also, we need to remember to use the REUSE() function to repeat any non-MultiValued data. So, our formula would look like the following:

SUBR("-CATS",SUBR("-CATS",SUBR("-OCONVS",EXTRACT(@RECORD,52,0,0),"D4-"),REUSE(" ")),SUBR("-OCONVS",EXTRACT(@RECORD,53,0,0),"MTS"))

(Note that this command would be entered all on one line)

Example 4

With Kourier Integrator for U2 successfully implemented on so many different application platforms, we've seen many different kinds of data structures. On one system, there was a date/time stamp in a single attribute, with the date in Value 1 and the time in Value 2. In this case, our formula would look very much like the first example (let's say that the data is in attribute 20):

OCONV(EXTRACT(@RECORD,20,1,0),'D4/'):" ":OCONV(EXTRACT(@RECORD,20,2,0),'MTS')

Of course, with any new I-Descriptor dictionary, you must compile the DICT before using it. In both UniData and UniVerse, you would do this with the following command:

CD filename dictionary_name

This will also tell you if you have made a mistake, like mis-counting the opening and closing parentheses (easy to do!).

With all of these examples, you should be able to tackle almost any date/time structure found in your application. As always, of course, we are more than happy to assist you in building these I-descriptor dictionaries!

We hope you find this tech tip as useful. As always, if you have any question on how to do any of this, please call us.

DataFlo and Preview are products, copyrights and trademarks of Epicor Corporation