DSXchange

In our current project we are extracting data from MYSQL database by using Unix Scripts then scheduled by CRONTAB. The out file is then transferred through PM4 Data Transfer to ETL server then it is loaded into TeraData Database.

Unix Script:
#!/bin/sh
#------------------------------------------------------------------------------#
#
Script name
# Author

# Created on : 06/04/2007
#
Modified by :
# Description :
#
Parameters: : None

#------------------------------------------------------------------------------#


USER=`grep -i "USER" .sh_param | awk 'BEGIN{FS=":";} {print $2}'`
HOST=`grep -i
"HOST" .sh_param | awk 'BEGIN{FS=":";} {print $2}'`
PASSWORD=`grep -i
"PASSWORD" .sh_param | awk 'BEGIN{FS=":";} {print $2}'`
DATABASE=`grep -i "DATABASE" .sh_param | awk 'BEGIN{FS=":";}
{print $2}'`
OUTPUT_DIR=`grep -i "OUTPUT_DIR" .sh_param | awk 'BEGIN{FS=":";}
{print $2}'`
TSStart=`date +"%Y%m%d%H%M%S"`
OUTPUT_FILE_NAME=$OUTPUT_DIR/ES3_ORDER_HEADER.txt

echo
"Select PK_ID
, LAST_UPDATE_DT
, WAREHOUSE_NBR
, OWNER_NBR
, SHIP_TO_CUSTOMER_NBR
, ZIP_5
,
ES3_REFERENCE_NBR
, ORDER_TYPE
, MFG_ORDER_NBR
, CUSTOMER_PO_NBR
, SHIP_MODE
, RDD_DATE
,
ORDER_RCVD_DT
, ALLOCATION_DT
, WHSE_LOAD_CLOSED_DT
, LOAD_TYPE
, ORDER_STATUS
From order_header
where
cast(last_update_dt as date) >= cast(curdate()-3 as date)" | mysql --user=$USER --password=$PASSWORD --database=$DATABASE | tr '\t' '|' >
$OUTPUT_FILE_NAME

--------------------

Mysql -> Crontab -> PM4 -> ETL -> TERADATA (this was designed in 2007)


Now we are planning to extract the data from MYSQL DB directly by using Datastage and load it into the target database TeraData.

MYSQL -> ETL -> TERADATA

Can I use JavaClient stage to connect MYSQL database and load it to Teradata? (JC-Transformer-TeradataMultiLoad)


Please suggest me the best solution

Note: Datastage version - 8.1

Can't you use ODBC stage to connect to MYSQL, any specific reason for Java Client ?

ODBC works perfectly well with MySQL, even when DataStage is on UNIX.

The DataDirect ODBC drivers support 5.0 and later versions of *MySQL* Commercial or *MySQL* Enterprise servers. They do not support any of the *MySQL* Community servers. You must use either the Commercial or Enterprise edition with the ODBC drivers.

Kalyan,
Thanks for your response, in our organisation they are using mostly JavaClient to connect MYSQL Database, I never used that stage before so i thought of use this stage as source to connect MYSQL.
Do you have any idea about this stage??

Ray,

Thanks for responding to my request, I never worked MYSQL as source before could you suggest me which stage would be better to serve my purpose (Javaclient or ODBC) as i explained above.

If I want to use JavaClient could you explain the advantages/disadvantages and same with ODBC.

Once again thanks for all of you.

I have only ever used the ODBC Connector with MySQL, so am not competent to comment on other stage types.

Me too never used JavaClient stage

in our organisation they are using mostly JavaClient to connect MYSQL Database

Check with them why they are using JavaClient.

As far as i know, Java stages are used only when you want to make use of some java programs/functions in Datastage.

If you just want to pull data from MYSQL and load into TERADATA, ODBC should be better.

Kalyan,

In Datastage 7.5 there was no stage to connect MYSQL, is it true?

No specific stage then or now, as far as I know. You would use the ODBC stage for MySQL.

Thanks every one