Table Definitions

[devilsmentor]

If i import the table definitions of a table, will the synonyms, indexes, constraints be imported too. If not, while running a SQL query from DS do i need to specify any particular command to involve indexes and all other related stuff with the table !!

[chulett]

If i import the table definitions of a table, will the synonyms, indexes, constraints be imported too.

No. It's not a modeling tool.

If not, while running a SQL query from DS do i need to specify any particular command to involve indexes and all other related stuff with the table !!

No, not for DataStage. And it's no different for DataStage than any other tool in that regard - it invokes the query and your database of choice decides on the 'execution plan', what indexes will be used, what order the tables will be joined in, etc etc. Same for synonyms, constraints, all that fun stuff - that's the domain of the database engine. You will have to keep them in mind so as to not... upset... the database when your job runs however.

Of course, you can influence those choices, just like you can with any other tool. In Oracle, for example, 'hints' can be used to override specific aspects of the query parser and explain plan. I would imagine that would be the same for pretty much any database out there.

You are typically better off writing well tuned queries without resorting to hints, they can come back to bite you in the butt later when the assumptions you made about query usage or the data topology changes.

[ray.wurlod]

If i import the table definitions of a table, will the synonyms, indexes, constraints be imported too. If not, while running a SQL query from DS do i need to specify any particular command to involve indexes and all other related stuff with the table !!

It depends upon how you import them (that is, via which protocol). As a general rule, primary key and foreign key constraints ARE imported (see the Relationships tab in the Repository table definition), but other constraints are not.

There would be no point in importing indexes. As far as the database server is concerned, a DataStage job is just another client application.

Some of the import tools allow you to check a box to cause synonyms and views to be imported, others do not.

[devilsmentor]

If i import the table definitions of a table, will the synonyms, indexes, constraints be imported too.

No. It's not a modeling tool.

If not, while running a SQL query from DS do i need to specify any particular command to involve indexes and all other related stuff with the table !!

No, not for DataStage. And it's no different for DataStage than any other tool in that regard - it invokes the query and your database of choice decides on the 'execution plan', what indexes will be used, what order the tables will be joined in, etc etc. Same for synonyms, constraints, all that fun stuff - that's the domain of the database engine. You will have to keep them in mind so as to not... upset... the database when your job runs however.

Of course, you can influence those choices, just like you can with any other tool. In Oracle, for example, 'hints' can be used to override specific aspects of the query parser and explain plan. I would imagine that would be the same for pretty much any database out there.

You are typically better off writing well tuned queries without resorting to hints, they can come back to bite you in the butt later when the assumptions you made about query usage or the data topology changes.

hey chulett,

thanks alot for the reply and you know wat i used the hints before posting this topic. but now i am bit scared when u said they are gonna come back to bite my $%# . Can you elaborate on this. thanks

and hey ray , thanks for the post but i am not enrolled in as a premium member, sorry i cannot see your content . but i will enroll in to view your reply. thanks 2 u 2

[chulett]

Doh! Interesting point about the 'Relationship' tab. Guess it pays to poke around more - in all honesty, I'd never looked there as I know the relationships for all of the tables I deal with, never mind the fact that I have other tools specifically for that kind of information. I import the metadata and then drag it into a job. Th-th-th-that's all folks.

Add that to Narasimha's 'right-click in the Layout tab' to get an HTML report of the table structure, that's two Things I Didn't Know about Table Definitions.

[chulett]

On the subject of hints - just be careful how you are using them. More 'generic' hints, for lack of a better term, can be fine but it's hard to make a 'generic' rule about their use. Sometimes specifying a particular table join order or 'nested loops' can be fine. But forcing a level of parallelism that may not be in the table itself? Forcing the use of a particular index? Just be careful of doing things to 'correct' a 'bad plan' that the database comes up with because now you are locked into that alternate plan. And as data volumes grow, table usage changes, yada yada - that plan that once made you look like a Rock Star may come back to circle around behind you for a tasty chunk. And you'll be back in the job re-hinting or un-hinting the queries.

I know I'm not explaining this all that well. Query tuning is kind of a Black Art, or at least it seems like it on some days. I generally find it best to work with competent DBAs when I have query performance issues and not just hint it into submission. You might find a 'better' way to perform the same query, or find that the statistics / histograms that drive the query optimizer are just plain wrong and the DBAs need to right that particular wrong.

[DSguru2B]

Well explained Craig. Guess your enjoying your weekend and are in a good mood.

[ray.wurlod]

The very first piece of advice I give on my DataStage training classes is "Explore! Right click everywhere!".

It's one of the ways I find things.

[kcbland]

From previous posts I'm assuming the poster is using Oracle. Oracle retired its Rule-Based-Optimizer (RBO) in favor of the Cost-Based-Optimizer). This means that the optimizer, using the table statistics, computes what it determines is the most beneficial data access path and methods for conducting its query. With the RBO, your hints were instructions to the optimizer, with the CBO, your hints are suggestions.

Some of the hints you may use could be ignored COMPLETELY. The CBO basically says when it's 50/50 which way it can go, look for a hint to guide. There are some hints that actually turn-off certain functionality (hash hints, etc) or environmental changes, but for the most part, you should favor current and accurate statistics over hints.

Some of the cases when you would favor a hint is when your hardware performance is detrimental to a specify query. One customer of mine had 18 older slow cpus on their system, but Symmetrix drives. For certain queries, it was actually faster to switch to a hash type join because the disks mashed the data faster than the cpus could do their thing according to the CBO. But we had a verrrry good DBA who helped with that very specific query. (Kirk D, you know who you are, as if you're lurking here anyway ) As a rule of thumb, trust the CBO from 9+. In 7 and 8 it was still kind of junky.

[devilsmentor]

thank you all guys, i really enjoyed having those helpful hands and wonderful suggestions .... i will try to find another way around to work without hints, also, i would try RIGHT CLICKIN EVERYWHERE. Thanks to ya all again !!!