topic Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to in Decisioning

Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

KidCat — Mon, 21 Jun 2021 17:22:56 GMT

Hi folks,

We are using Viya's SAS Intelligent Decisioning.

I woulld like to do publish from SAS to ORACLE (updating) by MAS using a DS2 package.

This doesn't seem to be very well documented.

Would you have some DS2 package code snippet how to do this?

Any pointer is very welcome.

TIA,

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

alexal — Mon, 21 Jun 2021 17:32:09 GMT

You have to use DS2 SQLStmt package. Please see some examples here https://go.documentation.sas.com/doc/en/masag/5.5/p1cnf8o1b4ez1dn1y0mlgrh1ory0.htm

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

KidCat — Tue, 22 Jun 2021 10:26:54 GMT

We're surprised we could use sqlstmt as it could not be used on CAS.

Way to go.

Thanks very much!

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

alexal — Tue, 22 Jun 2021 11:11:31 GMT

You're welcome.

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

KidCat — Tue, 22 Jun 2021 13:44:18 GMT

For whatever reason we cannot get it running.

Does anybody have possible a completely stripped-down code snippet that updates just a single a numeric Oracle column?

That would possibly rule out other root causes.

Kind regards, guys

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

alexal — Tue, 22 Jun 2021 13:47:56 GMT

Could you provide more information other than "cannot get it running"? Do you see any errors in the logs?

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

KidCat — Tue, 22 Jun 2021 13:56:42 GMT

too many.

we don't have the orignal data. so, we just replaced the VALUES expression 'insert into ora.... values (?,?), by a SELECT expression like 'insert into coldtemps select * from where...' Just something based on SASHELP.CLASS to get it running. Nothing fancy. We can make it complicated ourselves. 🙂

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

alexal — Tue, 22 Jun 2021 14:03:02 GMT

What kind of errors?

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

KidCat — Tue, 22 Jun 2021 14:31:48 GMT

I think this way it is simpler

package vmassst2;

dcl package logger logr('App.Test');

dcl package sqlstmt ss;

dcl package SQLStmt srollback;

method insert2( int quantity,

in_out int rc );

if null( srollback ) then do;

srollback = _NEW_ sqlstmt( 'rollback' );

if null( srollback ) then do;

logr.log( 'e', 'SQLStmt ROLLBACK instantiation failed.' );

rc = -1;

goto Exit;

end;

if null( ss ) then do;

ss = _NEW_ sqlstmt( 'insert QUANTITY into ora_dstest1.vmassst2 select QUANTITY from MY_SAMPLE_TBL where QUANTITY is not missing' );

/* not tested. Logic: insert value QUANTITY of MY_SAMPLE_TBL in place of value QUANTITY of VMASSST2 */

if null( ss ) then do;

logr.log( 'e', 'SQLStmt insert instantiation failed.' );

rc = -1;

goto Exit;

end;

rc = ss.execute();

if ( rc ) then do;

logr.log( 'e', 'execute failed. rc=$s', rc );

goto tRollback;

end;

tRollback:

logr.log( 'd', 'Rolling back uncommitted work...', k );

srollback.execute();

end;

Exit:

end;

endpackage;

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

alexal — Tue, 22 Jun 2021 14:50:32 GMT

What do you see in the logs?

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

KidCat — Tue, 22 Jun 2021 15:11:05 GMT

I cannot provide real errors and log due to compliance.

I know this isn't very helpful, I am sorry.

If the logic of that stripped-down code is not too far from wrong than we can shift our attention on data or environment.

As usual, it's the tiny things...

Thank you very much for your efforts.

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

alexal — Tue, 22 Jun 2021 15:18:06 GMT

If you cannot provide the logs here, then I would recommend you to contact SAS Technical Support https://support.sas.com/en/technical-support/submit-a-support-request.html where you can upload the logs to a secure location. I'm sorry, I won't be able to help without seeing what is going on in the logs...

Re: Viya SAS Intelligent Decisioning: Publish to MAS via DS2 Package: How-to

KidCat — Wed, 23 Jun 2021 11:37:56 GMT

VALIDATE says the basic error is that the package method sqlstmt.sqlstmt call is not supported by DS2 in CAS.