Data Access Methods

danjm · Post by **danjm** » Fri Sep 26, 2003 1:47 pm

I'm just new to DataStage and I have a question.
We are developing data 'registries' that have API interfaces for applications to communicate with/through. I believe these are implemented as COM Objects (we're mainly a Windows/SQLServer shop). Can anyone tell me if DataStage can access data stores through APIs instead of directly accessing the databases and what the performance and complexity issues might be[?]
I want to say that it makes the best sense to connect directly to the databases for data conversion and movement functions, - but others here are trying to force a "controlled access" model.
Any comments, experiences or 'pointers to related information' would be greatly appreciated. Thanks in advance... Dan.

Dan Marshall
Alberta Learning
Edmonton, AB

ray.wurlod · Post by **ray.wurlod** » Sat Sep 27, 2003 12:21 am

On Windows platforms DataStage can make use of "external functions" (exposed from ActiveX controls) as transformation functions. You will find that performance leaves something to be desired.
The BCI functions available within DataStage BASIC permit you to access SQL Server (and other databases) via an ODBC protocol.
There is a thing called the General Call Interface that allows your DataStage BASIC to call external functions (typically written in C, and necessarily written to be C-callable - for example a fixed number of arguments).
In terms of connectivity for extracting, browsing or loading data or importing metadata, you can create your own stage types (the stage itself is written in C, the GUI in C++). The extra layer(s) of software in your "controlled access API" is likely to prevent DataStage from reaching its full throughput potential.
All of these techniques are, of course, in the "advanced" category; you would not undertake them without specialist training and/or advice.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

mhester · Post by **mhester** » Sun Sep 28, 2003 11:01 am

Just a word of caution regarding coding to the GCI. Even though some manuals have outlined how to accomplish this, beware that you will be altering the very foundation that allows DS to work. Done incorrectly you will be left with a server that will not run or give extremely unpredictable results. Not to mention that you will receive no support for implementation of such routines. You would be essentially rebuilding uv and uvsh to recognize your new function and this is not for the faint of heart. This is not the same as writing a plugin since there are published guidelines for doing this and there may even be some form of support for doing so.

There are an entire list of things that have to be considered when doing this such as, what version of the C compiler was used to create the shell etc...

Long and short, it can get very tricky and really should not be undertaken by someone who is not completely confident in their C/C++ and Unix skills.

Regards,

Michael Hester

vmcburney · Post by **vmcburney** » Sun Sep 28, 2003 7:19 pm

If your team is embarking on a COM Object rollout they should investigate the lifespan of this approach. Microsoft are moving away from COM objects and into web services using the .Net framework. DataStage 6.0 has limited COM object support while DataStage 7.0 comes with extensive web services support.

It's a tough call to move away from COM objects since your developers are probably quite familiar with them by now.

Vincent McBurney
Data Integration Services
www.intramatix.com

ray.wurlod · Post by **ray.wurlod** » Sun Sep 28, 2003 8:25 pm

In fact part of installing the DS 7.0 client is installing .NET framework if it's not already in place!

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

danjm · Post by **danjm** » Mon Sep 29, 2003 8:21 am

Thank you all very much for your input on this question. It is very helpful to have the benefit of your combined years of experience. I will take your advice 'to the table' as we determine our standards for accessing data in a "COM" or ".NET" environment. I hope everyone has an enjoyable day and thanks again.

Dan Marshall
Alberta Learning
Edmonton, AB

vmcburney · Post by **vmcburney** » Mon Sep 29, 2003 5:30 pm

I was recently on a project creating COM objects to interact with Hummingbird ETL. Unfortunately I had the basic edition of Visual Basic .Net and it cannot create COM objects! Visual Basic 6.0 was no longer available in the stores. The documentation only explained how to create web services.

You may find that you can build these data registries as DataStage web services, with some basic validation and integrity checks and reject handling, in a fraction of the time it would take in VB. You can use VB .Net to convert calls from your legacy applications, which are not .Net compatible, into web services requests. I would try and get your VB gurus interested in doing web services in both VB and DataStage, they should jump at the chance of updating to the latest technology.

Toyota just announced a major web services project today, unfortunately I don't think they are an Ascential customer:
http://australianit.news.com.au/article ... 5E,00.html

Vincent McBurney
Data Integration Services
www.intramatix.com