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

[KidCat]

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,

KC

[alexal]

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

[KidCat]

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

Way to go.

Thanks very much!

KR

[alexal]

You're welcome.

[KidCat]

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

[alexal]

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

[KidCat]

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. 🙂

[alexal]

What kind of errors?

[KidCat]

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;

        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;

        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;

[alexal]

What do you see in the logs?

[KidCat]

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.

[alexal]

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...

[KidCat]

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