We use Datastage's Teradata connector to load the datamart tables using MultiLoad. However, with growing volumes and processes, it has been seen that the loading itself is taking a long time (e.g. > 2 hours for a 40-million load). While using the Native scripts, however, the load time has decreased drastically and we see a good performance improvement in our ETL batches.
The only challenge now would be to convert all Datastage jobs into native MLOAD scripts, which is a huge effort considering the number of processes/tables those are there. I was trying to explore if there'd be an easier way that can be achieved using certain re-usable components. I was wondering if there'd be a way wherein the different sections of the MLOAD script can be parameterized. so, e.g. if I am able to pass the layout during runtime as a file and thereafter the DML too be parameterized.
Any suggestions please
With best regards
I was able to figure out a way how to create a Multi-load script on the runtime. I am doing this by reading the structure of the file on runtime and "echoing" the different components of the mload script into a script file. As in, I create a script called as ABC.mload and in the next command in the wrapper script, I call using:
mload < ABC.mload
However, the challenge I face now is that I am not being able to secure the password. For login, I am using the standard login statement - .logon SERVER/Username,Password.
Another challenge is that I am not allowed to save the password anywhere on the unix box. Any suggestions please.
Hello TDThrottle - We are trying to use TDWallet for our DataStage based connectivity to Teradata but unable to do so. It is working fine with all command line based connectivities but could not get it to work with DataStage jobs. Can you please share your experience on how you got this working and point me to a place where I could get additional info?
The connector could not access the Teradata server dbtest with user $tdwallet(user). RDBMS code 244
Re the original post: If there is that large a difference in performance, it sounds like the ODBC method is being used rather than the TPT LOAD or TPT UPDATE methods. Datastage is capable of using TPT LOAD (fastload) or TPT UPDATE (multiload) directly using paramters on the connection.