Bulk orchestrate import of table definitions

lewap02 · Post by **lewap02** » Wed Jun 04, 2014 12:59 am

Hello. The question is about DataStage Designer client.

Is there any way to import many table definitions through orchestrate import (Import -> Table definitions -> Orchestrate schema import) "at once"? By "at once" or "bulk" I mean without importing every table separately.

Orchestrate import is my preferred method but it gets time consuming when I want to refresh tens of table definitions after making changes to the database.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 04, 2014 2:01 am

Welcome aboard.

At current version the answer is "no".

lewap02 · Post by **lewap02** » Wed Jun 04, 2014 2:15 am

Thank You for Your answer.

One time I heard about some Java API for DataStage and I hoped that this could be the way - to write some Java app that would loop through database tables and call orchestrate import.
Do You know if it is possible? Or maybe this Java API is not meant for such purpose?

chulett · Post by **chulett** » Wed Jun 04, 2014 7:22 am

That API allows integration of Java into a DataStage job. What you are looking to do doesn't require any sort of "job" to accomplish so while the API may not be relevant that doesn't mean you couldn't do this in Java itself. As noted, you just need something to retrieve a list of tables and then call the import in an iterative fashion. You could do that with Java, Perl, Korn or whatever else you were comfortable with.

lewap02 · Post by **lewap02** » Wed Jun 04, 2014 7:31 am

Could You give me a bit more detail? Of course I can get the list of tables with Java, that's not a problem, but do You know how to call orchestrate import from outside DataStage Designer (with appropriate set of parameters)?

chulett · Post by **chulett** » Wed Jun 04, 2014 7:52 am

From the command line... the utility is named "orchdbutil" from what I recall. Pretty sure that's a practical approach, I'm sure someone else will come along and comment on it for us.

lewap02 · Post by **lewap02** » Thu Jun 05, 2014 5:02 am

I found information which, I think, would be enough for me to create tool for mass orchestrate import but one thing stands in my way:

it seems like orchdbutil is only on the server side. Do You know if one can run it from the client side? I would like very much to avoid any remote calls because it would create many limitations.

ArndW · Post by **ArndW** » Thu Jun 05, 2014 6:06 am

It will only run on the server, not on the client.

chulett · Post by **chulett** » Thu Jun 05, 2014 6:52 am

The simplest way to avoid any remote calls is a script that runs on the server... is that not an option for you?

lewap02 · Post by **lewap02** » Sat Jun 07, 2014 6:26 am

I see that I ain't got much choice. Ok, I can run script on the server directly. I started putting pieces together but now ran into something like (while trying to call orchdbutil):

"(...) Could not find orchoracle on the library search path (...)".

I do have path ...PXEngine/lib in LD_LIBRARY_PATH so it's not the case but I noticed something worse: there is no *orchoracle* in that path. More: there is no such file on the server...

Could this mean that my DataStage installation is somehow corrupted? I must add that I am able to do orchestrate import from oracle database in the DataStage Designer.

chulett · Post by **chulett** » Sat Jun 07, 2014 7:29 am

lewap02 wrote:Could this mean that my DataStage installation is somehow corrupted?

I doubt it. Have you tried / been able to do an import from the command line manually, as in outside of your script?

lewap02 · Post by **lewap02** » Sat Jun 07, 2014 7:52 am

By "as in outside of your script" You mean to type "orchdbutil ..." directly to the Linux command line? If yes then this is exactly what I do.
Before I put it into any script I want have it going for a single table and I'm running it from the command line.

chulett · Post by **chulett** » Sat Jun 07, 2014 10:41 am

OK, that's good. You might be surprised at how many people don't do that first to ensure they have the syntax working before it gets scripted. And with that working for you, it rules out your installation being any kind of corrupted.

Then it would seem you just have an 'environment' issue and those are typically solved by sourcing your dsenv file as one of the first steps in the script. Can you try that or let is know if you are doing that and it is still not working?

lewap02 · Post by **lewap02** » Sat Jun 07, 2014 1:03 pm

I've managed to get it running after putting dsenv at the beginning of the script. Thanks for that advice.

One thing I need to figure out (I guess these are basics but I'm not strong with Linux) is why it does work only when I copy-paste the whole content of dsenv into my script. It doesn't work when I put only one line with call of dsenv. It looks like in the latter situation the variables are not set...

The next step is to make an actual import of the single table (becuase as for now I've done it in "check" mode) and finally put it into loop.

lewap02 · Post by **lewap02** » Sat Jun 07, 2014 1:39 pm

I've dug into topic a bit deeper and understood one thing: orchdbutil will not make the import

It will only show me the schema definition.

Now, one of the approaches I came across is to parse the output of orchdbutil and save it in .dsx format. Then it can be imported using dscmdimport.

It is not very hard but I hoped for more elegant solution :D And less time consuming. Can You think of any?