DI Studio 4.2: creating indexes in user written code

[deleted_user]

Hello!

I'm designing a transformation which replaces target table and creates index on it. Target table, that is connected to output port of transform has defined index, but I can't write the "create index" statement in the transformation, because there is no any information about indexes in macro variables, automatically generated in my transformation.

How can I get information about index on target table from transform?

[LinusH]

If you want to rely on what definitions resides in metadata, you'll probably have to connect to the metadata server and retrieve the index spec using the functions metadata_getnobj, metadata_getattr and metadata_getnasn.

/Linus

[Patrick]

"...a transformation which replaces target table and creates index on it"...

Why can't you use a table loader using a "replace" option?

[deleted_user]

Patrick, source and target tables are both stored in relational database, and table loader passes data through SAS, and this causes extra data transmissions between servers. My transform only passes "create table as select * ..." to database.

[Patrick]

Igor

I'm not 100% sure that if source and target are in the same DB that the code generated by the "Table Loader" results in data passing via the SAS Server from Source to Target.

A lot of SAS code is translated into DBMS specific SQL during SAS execution. I would know the option for Oracle to get in the SAS log what's really sent to the DB for execution (what DBMS are you using?).


Another approach would be to use a "SQL Join" transform.

I did the following test:

1. Job with Source and Target table (index on target table)
2. "SQL Join" transform
3. Connect Source to "SQL Join"
4. Delete output table on "SQL Join" (this green little table on the right side of the transform).
5. Connect "SLQ Join" to Target
6. In "SQL Join" designer: Delete "Join" object.
7. In "SLQ Join" designer: Connect Source to "Select" object

The resulting code looked good to me. Both the PROC SQL part creating and loading the target table and the PROC DATASETS part creating the index after the data gets loaded looked fine to me. The code will execute on the DB.


I believe to use a "User Written Code" node or to create a "User Tranformation" should be the last measure if there are no other means to solve a DI challenge.
.....I already created quite a lot of both of these objects.... 😉

In your case: It's not only about indexes but also about constraints. The only way I can think of to create this manually is to query the metadata and then based on the result to create the code on-the-fly. This would be quite a task.

HTH
Patrick