MS SQL Server Load stage equivalent in PX in UNIX??

splayer · Post by **splayer** » Tue Aug 22, 2006 8:22 am

I have DataStage 7.5.1A, Parallel Extender, UNIX version.

What is the equivalent stage of MS SQL Server Load stage in this version. The installation guide says that this plug-in stage is not available in the parallel canvas. Does that mean it is not available in this version? When I installed the client on my machine, I installed everything that was possible. I even clicked on Custom to make sure that everything was being installed.

Thanks to anyone who responds.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 22, 2006 8:25 am

Sequential File stage which you then FTP to Windows and initiate the bulk load into SQL Server.

samba · Post by **samba** » Tue Aug 22, 2006 8:25 am

you can use ODBC or Dynamic RDBMS stage to load the into MS SQL Server.

Thanks
samba

splayer · Post by **splayer** » Tue Aug 22, 2006 8:59 am

ray and samba, thank you very much for your responses.

ray, can you elaborate little bit more? So, I load to a Sequantial File stage.
I then use the FTP Plug-In stage to FTP the file to a Windows folder. Now, do I have to have another job that'll load this file into SQL Server using the bulk load stage? This seems like a lot of data movement. Will performance be a problem? Also, I have to switch between Parallel DS/UNIX to Parallel DS/Windows. Is this correct?

samba, if I use ODBC_Enterprise stage, what would the value of the data source be. I think I have to load a DSN on the server where Datastage is installed. However, it is a UNIX box not Windows. Can you tell me how I go about it?

Thanks.

kcbland · Post by **kcbland** » Tue Aug 22, 2006 9:22 am

You create the load file on your PX Unix server. Then, FTP to the SQL-Server machine and use ssh or remsh to kick off a remote bulk load execution.

DSguru2B · Post by **DSguru2B** » Tue Aug 22, 2006 7:18 pm

The SQLServer Load stage is not available for unix platform, thats why this workaround of building the flat file, ftp'ing it over to the windows server and then invoking the bulk loader.

jdmiceli · Post by **jdmiceli** » Wed Aug 23, 2006 2:30 pm

Hi all,

Samba stated that you could use the Dynamic RDBMS stage to insert data to SQL Server, but I have had no luck with this at all. I was told by a contractor that worked with us when we first got DataStage that particular stage could read data from SQL Server, but could not write to the database engine. When I plied him for an explanation, he just shrugged his shoulders and said that's the way it is (one of many reasons his career disipation light went into high blinking mode at our company

)

Is this truly the case? If not, I'd sure like to know what we have done wrong that prevents inserting/updating targets with it. The reason I would like that functionality is that we will be doing system upgrades in about a year moving the target databases from SQL Server to DB2 UDB on AIX and it would be nice to just change the database selection on the first tab of the existing stage as opposed to reworking each job (if that theory would work anyway that is).

Thanks!

kcbland · Post by **kcbland** » Wed Aug 23, 2006 3:15 pm

I don't know why the Dynamic stage can't read and write on a Unix platform, it's just ODBC. As for bulk loading, you have to have the command line utilities and since those are Windoze based, you need to manually handle calling it locally on the database server.

Beware the marketing gimmick about toggling DB's in the Dynamic RDBMS stage and you're done. Datatypes are not transportable across databases, reserved column names can conflict, column name length limits come into play, and database functions and the WHERE clause syntax will cause you plenty of heartache.