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

Differences in generated code - unnecessary constraint creation step

Reply
Occasional Contributor
Posts: 6

Differences in generated code - unnecessary constraint creation step

My colleague and I are seeing different code generated for the same job.

I open a job on my Win7 machine using DIS version 4.21, and open a join transform to view the code. Right at the end of the join I see the following:

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

%macro etls_completeTarget();

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

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

   proc datasets library=work nolist;

      modify Note_0;

         ic create not null (BI_Row_Effective_Date);

         ic create not null (Note_Id);

         ic create not null (Account_Id);

         ic create not null (Created_By_User_Id);

         ic create not null (Updated_By_User_Id);

         ic create not null (BI_Row_Deleted_Ind);

         ic create not null (Security_Type_Desc);

         ic create not null (Note_Topic_Desc);

   quit;

  

   %rcSet(&syserr);

  

%mend etls_completeTarget;

%etls_completeTarget;

My colleague opens the job on her machine (same version of DIS as far as we can tell), and the code for that join finishes without the etls_completeTarget macro definition, like so:

%global etls_sql_pushDown;

%let etls_sql_pushDown = &sys_sql_ip_all;

%rcSet(&sqlrc);

Does anyone know any reason for the difference we're seeing? Given the size of the (temporary) table being created here there is potentially quite a large performance penalty to be paid for what seems to be an unnecessary step. When viewed through the diagram, the join looks identical.

Super User
Posts: 7,809

Re: Differences in generated code - unnecessary constraint creation step

Posted in reply to geoff_ness

Do both of you work with the SAME metadata? (identical metadata server, identical port)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Differences in generated code - unnecessary constraint creation step

Posted in reply to KurtBremser

Yes. We opened the job from the same metadata location.

Super User
Posts: 5,431

Re: Differences in generated code - unnecessary constraint creation step

Posted in reply to geoff_ness

And Identical meta data id? Do you work with change management? Do you have write access when opening the job? What happens if you deploy the job?

Data never sleeps
Occasional Contributor
Posts: 6

Re: Differences in generated code - unnecessary constraint creation step

Do you mean metadata id for the job? I will check tomorrow when I get back into work. We do work with change management but on this occasion opened the job with read access only.

When I deploy the job into our development environment, the additional code is preserved, whereas when my colleague deployed the original job it wasn't (i.e. she just saw the same code as when she viewed the job in DIS). We haven't yet tried the release process which involves exporting the job from development into a package then importing the package into production.

Respected Advisor
Posts: 4,173

Re: Differences in generated code - unnecessary constraint creation step

Posted in reply to geoff_ness

It is the DI Studio client which generates the code based on server side metadata.

I remember having worked in a project where the patch level of a DIS4.2 version made a difference in the code generated.

Are you 110% sure that the two DIS clients are of the same version and have the same patches applied?

Assuming that you're both using the same metadata (so not one of you the job in a "checked-out" version) then:

If you migrate the job to a higher environment, you migrate metadata from server to server. The patch level of the client shouldn't affect the .spk package. But: When you open the job in the new environment and look at the generated code (generated by the DIS client) then the patch level of the client can matter.

So if the two DIS clients are not identical (version and patch level) then I would expect to see the code differences with metadata coming from any environment.

Occasional Contributor
Posts: 6

Re: Differences in generated code - unnecessary constraint creation step

Thanks Patrick, yes the clients are definitely the same version, but there is a difference in the java runtime - my JRE version is 1.6.0_17 whereas my colleague's is 1.6.0_20. Another colleague with the same JRE version as me gets the same generated code as I do, so this difference looks to be the most likely culprit.

Occasional Contributor
Posts: 6

Re: Differences in generated code - unnecessary constraint creation step

Posted in reply to geoff_ness

This looks to be incorrect. Yet another colleague with the same JRE version (and same DIS client version) does not get the additional step.

Super User
Posts: 5,431

Re: Differences in generated code - unnecessary constraint creation step

Posted in reply to geoff_ness

What do you mean by development environment with regards to "original job"? Where is that?

It sounds like you are not comparing the same job.

Data never sleeps
Occasional Contributor
Posts: 6

Re: Differences in generated code - unnecessary constraint creation step

Sorry I know that answer made it sound like that. No, we are comparing the same job - when I refer to the original job, I am talking about a job which was released to production about a year ago. As opposed to the job which I am now working on in development, which I have made some changes to. It was the process of comparing the deployed code from different environments that led to further investigation. We are now comparing the code that is generated by DIS when we open the production job, me on my DIS client and my colleague on hers - this is where we see differences that we don't expect, as we are definitely looking at the same metadata object.

Super User
Posts: 5,431

Re: Differences in generated code - unnecessary constraint creation step

Posted in reply to geoff_ness

Still not convinced by your description that you are looking at the same object. But if you are, and this is a problem for you, I sugest that you open a track to SAS tech support, they could help you trouble shoot this.

Data never sleeps
Ask a Question
Discussion stats
  • 10 replies
  • 699 views
  • 6 likes
  • 4 in conversation