Re: Importing data into MS-SQL from AcuCobol

From: JJ (jj_at_nospam.com)
Date: 10/25/03 

Date: Sat, 25 Oct 2003 16:43:49 -0400

A few suggestions:

- If your database tables have any indexes, drop them before loading the
data, then recreate them after it is loaded. This is a good idea no matter
how you are loading the data.

- Look into using DTS (Data Transformation Services) in SQL Server. I have
not used it with AcuODBC, but it should work. You can create a DTS package
that will read data from the Vision files (using AcuODBC) and store the data
in SQL Server. You can script the whole thing and even schedule it to run
automatically.

- The vendor's suggestion of creating export files is not a bad one. If you
can get the data into delimited files, ideally that mimic the table layouts
exactly, you can execute the SQL Server "bulk insert" statement or the bcp
utility, which loads data into SQL Server very quickly. (One tip - with the
"bulk insert" statement, the filename you reference needs to exist on the
system were SQL Server is running.)

It shouldn't matter whether there are 2 or 500 files - if you have a single
program that creates all 500 files, you can set up a single SQL script with
a "bulk insert" statement (or a batch file with "bcp" commands) that will
load all 500 files.

"jpo" <asdf@asdf.com> wrote in message
news:3f9aa05e$0$23566$41ce378f@news.swiftel.com.au...
> Hi there people,
>
> I've recently been dumped in at the deepend at a company which needs to
> import data from a product system using AcuCobol data files. The data is
> currently stored as data files and xfd files, and we are using AcuODBC as
he
> interface to load the data.
>
> This is ok except that it is VERY slow - when I say slow it is currently
> take a matter of hours and it SHOULD really only take about 20-30 min for
> the volume of data we are using.
>
> What is really the best way to move forward with this? I'm not getting a
lot
> of help from our partners here in Australia and I just feel I am spining
my
> wheels because I don't have the experience.
>
> We intitally tried loading two tables (data files) at once and it
> continuously crashed giving a type mismatch error - which was rubish
because
> it ran fine in serial. After much fiddling around I worked out you could
> load two or more tables in parrallel if you created two or more odbc DSNs,
> but this just meant that the loads of each table ran twice as slow, so
> instead of two files taking 5 min each in serial they took 10 min in
> parrallel.
>
> The files were orriginally being accessed across a network share, so I had
> the NIC interface upgraded to 1gig - this didn't change things at all, so
I
> arranged for the files to be delivered to the local harddrive - this
changed
> nothing as well.
>
> I have been trialling AcuODBC Server but I don't think this is the tool I
> want either, and besides it seems to be throwing an incorrect type
> conversion error when I use the AcuODBC Server instead of a direct AcuODBC
> connection. Our vendor here suggested Acu4GL, but from my reading this
> appears to be a tool to read SQL RDBMS systems from a COBOL front end,
> rather than load mass volumes of data from a VISION file into a SQL DWH.
>
> Our vendor very kindly offered to build a program for each file that would
> dump the data down to an ascii text file. For over 500 files I told them
> "thanks - but no thanks".
>
> I'm really out of ideas here and I'm getting kind of desperate, if anyone
> has any help I would be greatfull.
>
> My line of logic would be to write a program or use an application I can
> script in Win2K to dump the contents of the data files using the xfd files
> provided. I could then import this ascii file extremely quickly into SQL
> server.
>
> I've tried vutil but it doesn't seem to be the horse for the race.
>
> Thanks very much in advance for any assistance anyone can offer.
> Regards
> JPO
>
>
>

Relevant Pages