topic Re: Differences in generated code - unnecessary constraint creation step in SAS Data Management

Differences in generated code - unnecessary constraint creation step

geoff_ness — Mon, 01 Sep 2014 09:49:21 GMT

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.

Re: Differences in generated code - unnecessary constraint creation step

Kurt_Bremser — Mon, 01 Sep 2014 10:48:41 GMT

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

Re: Differences in generated code - unnecessary constraint creation step

LinusH — Mon, 01 Sep 2014 11:17:21 GMT

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?

Re: Differences in generated code - unnecessary constraint creation step

geoff_ness — Mon, 01 Sep 2014 12:09:06 GMT

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

Re: Differences in generated code - unnecessary constraint creation step

geoff_ness — Mon, 01 Sep 2014 12:18:47 GMT

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.

Re: Differences in generated code - unnecessary constraint creation step

Patrick — Mon, 01 Sep 2014 12:40:43 GMT

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.

Re: Differences in generated code - unnecessary constraint creation step

geoff_ness — Mon, 01 Sep 2014 21:55:15 GMT

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.

Re: Differences in generated code - unnecessary constraint creation step

geoff_ness — Tue, 02 Sep 2014 01:45:41 GMT

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

Re: Differences in generated code - unnecessary constraint creation step

LinusH — Tue, 02 Sep 2014 13:47:58 GMT

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.

Re: Differences in generated code - unnecessary constraint creation step

geoff_ness — Tue, 02 Sep 2014 21:27:46 GMT

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.

Re: Differences in generated code - unnecessary constraint creation step

LinusH — Wed, 03 Sep 2014 06:06:42 GMT

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.