SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Issue with creating indexes for a migrated job

Reply
Occasional Contributor
Posts: 9

Issue with creating indexes for a migrated job

I am facing some issues with a job which I have migrated from SAS 9.2 to SAS 9.4. The job runs fine but it does not create indexes for some SAS datasets which are created/updated by it.

The job has some template tables which create a few data sets with a atime stamp. every time the job runs. I copied the entire metadata from the SAS 9.2 environment to SAS 9.4 environment to ensure the index and key mappings are preserved.

Also, we tried creating the indexes manually and were able to create them. There is a dependent job which needs the indexes as the datasets are sorted using the BY clause. The job fails without the indexes but runs fine after we created the indexes manually.

Please help as we are not able to migrate to the new environment due to this issue.

Respected Advisor
Posts: 3,892

Re: Issue with creating indexes for a migrated job

Not even knowing which transformation should be creating the indexes it's impossible to give you concrete advice.

What I would be doing in such a situation:

- Identify the node which should create the index

- Have a look into the generated code of this node to see if there is some code generating indexes - and what conditions are set

- Check the migrated code node: What are the options in this transformation for index creation (like: "re-create", "leave as is",....)? What's set in the source environment, what's set in the target environment.

And just a thought:

Are you fully re-creating the physical data set in the target environment based on metadata or are you also moving the physical SAS file? If the latter: Are you also moving the indexes which are stored in a separate file? If your DI transformation would have an "as is" set for index creation then indexes wouldn't get re-created in the target environment.

Occasional Contributor
Posts: 9

Re: Issue with creating indexes for a migrated job

We have a number of SQL joins in the job. Each SQL joins has a target table for which indexes are should be created and we are using the BY clause for sorting. For all these target tables apart from one, the indexes are not getting created. We have looked into all the nodes(including sources tables, target tables and transformations for all the joins) but could not find anything conclusive.

To elaborate upon the migration, we had first migrated the job with dependencies(not all but partially). There are a few template/temporary tables which create data sets with time stamps. We took all of them except the ones which we could physically migrate and create the metadata. During our first run, we found that we had missed creating the meatdata for these migrated datasets. We then, created the metadata and re-migrated the jobs. But we found that the same issue persists.

We then analyzed the tables for thich we had created the metadata and found that the indexes were not present for these tables. We then, created the indexes manually, but after re-running the job, the index creation fails again. There are not errors though.

Finally, we imported the metadata for all these tables from the old environment and tried to run the job again to find the same problem.

Do you need more inputs here ?

We have tried looking at the code but will have a look again to see if we have missed something.

Respected Advisor
Posts: 3,892

Re: Issue with creating indexes for a migrated job

Looking at the generated code will give you all the answers.

1.Is there some code generated which creates indexes?

1a. if there is no code generated to create indexes - why not? Does the metadata used not have the indexes defined?

1b. If code for index creation: Only if table does not exist or always? If only if table does not exists: Have you eventually migrated the underlying physical table without the indexes?

We then, created the indexes manually, but after re-running the job, the index creation fails again. There are not errors though.

Again: I'd look first at the actual code generated. Does this explain what happens? If yes: Which option in the transformation would you have to "click" so that code gets generated which behaves the way you want to? Does the table metadata have the indexes defined?

Best test:

1. Make sure table metadata has the indexes defined.

2. Make sure the physical .sas7bdat file does not exist.

3. Run the DI code. If this doesn't create the table with the indexes then check the generated code to explain why indexes are missing.

Occasional Contributor
Posts: 9

Re: Issue with creating indexes for a migrated job


We did migrate the table with indexes. The metadata for the table has the indexes defined. It seems we are facing some issue with the SQL joins in the job.

We have 5 SQL joins in the job along with a table loader which generate indexes for the target tables. The Table loader is able to genrerate the index files but the issue seems to be with the SQL joins only. I am attaching a part of the job code which actually creates the index files. I do not see this in the migrated job.

%macro etls_completeTarget();

   /*---- Create the integrity constraints for a table  ----*/

   %put %str(NOTE: Creating integrity constraints ...);

   proc datasets library=credits nolist;

      modify &KEYTABLE.;

         ic create not null (COL1);

         ic create not null (COL2);

         ic create Index1 = primary key (COL1 COL2);

   quit;

  

   %rcSet(&syserr);

  

%mend etls_completeTarget;

The bold portion does not appear in the

I am not sure if this macro is custom/inbuilt with SAS. Please guide me on this.

I have been suggested to manually add these lines to the code but I feel the issue lies somewhere else as the migated job generates index for one of the tranformations but not the other.

Respected Advisor
Posts: 3,892

Re: Issue with creating indexes for a migrated job

This macro is autogenerated by DIS if there is a target table with indexes defined. If you don't get this code in your target environment then I'd check the following in the SQL node:

- is code generation set to "autogenerate"?

- has the target metadata table of the SQL join such constraints and a PK defined?


I agree with you that adding the code manually should be the last resort as this would mean that you would have to change over the node to "manually written" code - something you should only do if you just can't make things work with auto-generated code.

I believe you should try and resolve the issue on metadata level: Check the table definitions that the PK is defined there, check the Join transformation that index creation is selected.

Occasional Contributor
Posts: 9

Re: Issue with creating indexes for a migrated job

The code is autogenerated. Can you please explain on the second point ?

The target tables exist in metadata only. We actually have a few models which run in identical stages. The target tables are sort of template tables and write the physical table for individual models with model numbers as reference(in couple of cases with timestamp as well).

During the migration from SAS 9.2 to SAS 9.4, I copied the jobs along with the dependencies i.e. these template, metadata tables. I can see the Primary Keys defined there but not sure on how to check the integrity constraints for these. I usually check for integrity constraints in E Guide but since, these tables are not physically present I cannot check for them.

Can you please elaborate on the second point :  has the target metadata table of the SQL join such constraints and a PK defined?

Respected Advisor
Posts: 3,892

Re: Issue with creating indexes for a migrated job

Has the target table of your transformation an index defined? Something like below:

Capture.PNG

Assuming you're using a SQL Join transformation: Is "Generate indexes on target tables" set to "Yes"?

Capture.PNG

If this doesn't resolve your problem then you will need to attach a .spk with the job and all the dependent objects like table metadata and libraries. If you're lucky then someone's got a suitable environment to import your .spk and have a closer look of what's going on.

Occasional Contributor
Posts: 9

Re: Issue with creating indexes for a migrated job

We have checked this already. It seems we have missed something while migrating the stuff from SAS 9.2 to SAS 9.4.

Respected Advisor
Posts: 3,892

Re: Issue with creating indexes for a migrated job

Does your answer now mean the issue is resolved? If yes: What has been the resolution? What has been missed?

Occasional Contributor
Posts: 9

Re: Issue with creating indexes for a migrated job

No. I have got the following reply from Support guys.

 

http://support.sas.com/kb/40/556.html

1. The behavior you are experiencing in 9.2/ DI Studio 4.21 with Constraint creation in the SQL Join transformation was an unintended behavior change that was introduced in 9.2 that negatively impacted customers existing jobs. that caused customer's job(s) ar

2. The issue was addressed in a 9.2 / DI Studio 4.21 hot fix (as documented in the above referenced SAS note) and in 9.3 / DI Studio 4.3. 

3. There is no way to make the SQL Join create Constraints as of 9.3 / DI Studio 4.3.  The only work around that I can come up with to resolve this issue  would be to take the Constraint creation code from their 9.2 jobs and add this to their 9.4 jobs in the PostCode section of the PreCode and PostCode tab under Properties for their SQL Join transformations.

It seems we will have to copy the code manually for the migrated jobs.

Respected Advisor
Posts: 3,892

Re: Issue with creating indexes for a migrated job

Hmmm... If I understand the SAS Note correctly then the issue reported is that there has been an involuntary introduction of index creation code. BUT: In your initial post what you state is that you had this index creation code already in the 9.2 source environment but now you are missing the same index creation code in the 9.4 target environment. As I read the SAS Note it doesn't explain the issue you're facing (no index creation in the SAS 9.4 code). What am I missing?

I strongly recommend to try and resolve the issue on DIS metadata level. Don't just copy the deployed jobs manually as this would render the DIS metadata unusable (you couldn't re-deploy jobs). Worst scenario would be to change code creation to "manually" and to "copy/paste" the required code "snippets" into the related code nodes. But also such an approach would mean that future job maintenance would get much harder (as then all changes would need manual code changes). I simply can't believe that there isn't a solution on DIS metadata level.

Occasional Contributor
Posts: 9

Re: Issue with creating indexes for a migrated job

Patrick,

As per the note's 2nd point, they have disabled the automatic integrity constraints' creation. Indexes will still get created. I did observe this for the output data sets for the job.

Also, we are facing issues with the SQL joins only. Table loader is working fine.

I agree with you that there has to be a solution to this. Till then, I am trying this work-around to see if it works. I believe it will work as it is as good as adding the integrity constraint creation snippet to the code manually.

Adding it to the post code, will keep my original job AUTOMATIC code creation intact.

Will update if this work around works fine or not. But, I am sure that there are many more such surprises/issues with the SAS 9.4 version.

Ask a Question
Discussion stats
  • 12 replies
  • 844 views
  • 0 likes
  • 2 in conversation