SQL Server: How to deal with Acucobol date fields

Tweet

As explained in a previous post, you can connect your Sql Server to an Acucobol db using AcuODBC. In the last part of the article I explained that there are performance issues when you query a linked server with OpenQuery and that it is better to import relevant data from the linked server to a Sql Server table in order to benefit from all the features available through stored procedures, triggers and so on.
I run some queries overnight  using SQL Scheduler to keep my local tables updated against the Acucobol tables, but, when importing the data, in some way I manipulate them to make them easier to query. What I found important in terms of performance, is to convert date fields from int to smalldatetime format. Furthermore, when importing data I insert into the newly created Sql Server table an identity field and set it as primary key.
Let's break down the query and I will explain it bit by bit.

Check if the linked server is available

First, I like to check if the linked server is available and ready to be queried. To do so, I use an If statement like this:

if (select top (1) count(*) FROM OPENQUERY(LinkedServerName, 'SELECT * FROM LinkedServerTable'))>0

What we are doing here, is a check on a table - whatever table you like - of the linked server and see if it returns at least one record. LinkedServerName is the name of your linked server and LinkedServerTable is the aforementioned 'whatever' table.

Drop the table and start the select into

I usually start the update of the table, dropping the old one. Remember that we checked the linked server availability, so we can proceed with the update. If the server is not available the query will immediately end after the if statement.

Begin
DROP TABLE dbName.dbo.NewTable
SELECT IDENTITY(INT, 1, 1) as id, * into dbName.dbo.NewTable FROM OPENQUERY (LinkedServerName, 'SELECT * FROM LinkedServerTable')

What we do here is basically:

1. delete the old table with the DROP TABLE command;
2. insert the new data using OPENQUERY, but adding a totally new identity column named id.

The dbName.dbo.NewTable is the name of the table we are creating. When you first run the query you will get an error saying that the dbName.dbo.NewTable couldn't be dropped because it doesn't exist. Well, that is true because it is the first time you create it. You can ignore that error.

Insert new date columns and set primary key

The next step is related to date fields. Depending on how many date fields you imported, we now add new columns, where we will store the date in smalldatetime format.

ALTER TABLE dbName.dbo.NewTable
ADD   NewDateTime SMALLDATETIME NULL, PRIMARY KEY(id)

With the above code, we altered the table and inserted a new column (NewDateTime) and then we set id as primary key.

Get the Acucobol date field and populate the NewDateTime field

When you import your Acucobol table, you transfer some date columns as int. I noticed that those fields are in the yyyymmdd format. When running queries for big tables, filtering data using those date fields could be really a problem in terms of performance. That is why we convert those fields - we actually duplicate them, but that's just a false problem, because you can always delete those columns afterwards. You can use this procedure whenever you need to convert those date formats.

UPDATE dbName.dbo.NewTable
SET [NewDateTime] = CONVERT(DATETIME, CONVERT(CHAR(8), AcucobolDate))
where AcucobolDate<>0

We are basically converting and copying data from one column to another.
As said before you can then drop the AcucobolDate column, if you like to get rid of unused data.

ALTER TABLE dbName.dbo.NewTable
DROP COLUMN AcucobolDate
End

And that's it.

The complete query

The complete query will be:

if (select top (1) count(*) FROM OPENQUERY(LinkedServerName, 'SELECT * FROM LinkedServerTable'))>0
Begin
DROP TABLE dbName.dbo.NewTable
SELECT IDENTITY(INT, 1, 1) as id, * into dbName.dbo.NewTable FROM OPENQUERY (LinkedServerName, 'SELECT * FROM LinkedServerTable')
ALTER TABLE dbName.dbo.NewTable
ADD   NewDateTime SMALLDATETIME NULL, PRIMARY KEY(id) UPDATE dbName.dbo.NewTable
SET [NewDateTime] = CONVERT(DATETIME, CONVERT(CHAR(8), AcucobolDate))
where AcucobolDate<>0 ALTER TABLE dbName.dbo.NewTable
DROP COLUMN AcucobolDate
End

The other side of the coin

I must add something in the end because many of you might have risen an eyebrow reading the above procedure. Why don't we convert the Acucobol date fields during importing? Good point! In my experience whenever I tried to convert the date fields during import I had big problems. Almost always there was an error in the conversion process. Usually it was either an overflow error or a problem in converting char or int into smalldatetime. Using the above procedure I noticed that all the possible error were gone, because I could have a better control on what was happening. On top of that, I can add that sometimes you might need to keep the original data and basically have the date fields duplicated.
In case you want to convert directly during the Select Into command you can simply insert the CONVERT(DATETIME, CONVERT(CHAR(8), AcucobolDate)) inside it like this:

SELECT IDENTITY(INT, 1, 1) as id, CONVERT(DATETIME, CONVERT(CHAR(8), AcucobolDate)) as [NewDateTime], * into dbName.dbo.NewTable FROM OPENQUERY (LinkedServerName, 'SELECT * FROM LinkedServerTable')

Let me say that I prefer to have control and do things one at the time, however if you feel like doing it all in one go, test it and see how it works.

Please share your thoughts in the comment section, if you wish to contribute.