Pause/Sleep/Wait

Elegant way to pause or sleep processing for a while without using CPU time. In PeopleCode:

Local JavaObject &thread = GetJavaClass("java.lang.Thread");
&thread.sleep(2500); // this is in milliseconds

Indexes on Delivered Tables

1. PSMENUITEM
create index psoft.MW_PSMENUITEM on psoft.PSMENUITEM ( PNLGRPNAME, ITEMNAME ) tablespace psindex;

2. PSPNLGROUP
create index psoft.MW_PSPNLGROUP on psoft.PSPNLGROUP ( PNLNAME, ITEMNAME ) tablespace psindex;

3. PSAUTHITEM
create index psoft.MW_PSAUTHITEM on psoft.PSAUTHITEM ( DISPLAYONLY, BARITEMNAME, PNLITEMNAME, CLASSID ) tablespace psindex;

4. PS_VAT_DEFAULT_HDR
create index PSOFT.MW_PS_VAT_DEFAULT_HDR on PSOFT.PS_VAT_DEFAULT_HDR ( VAT_DRIVER, COUNTRY, STATE, VAT_DRIVER_KEY1, VAT_DRIVER_KEY2, VAT_DRIVER_KEY3, EFF_STATUS, EFFDT, END_EFFDT, VAT_DRIVER_KEY4, VAT_DRIVER_KEY5, VAT_SETID );

Selecting CLOB Fields

Can be hard if you are using DISTINCT or LIKE clauses et al in your statement. Try this Oracle function:

SELECT
dbms_lob.substr(j.comments_2000,1,1000)

Publishing Files to Report Manager/RDS

See also: http://capital-it.blogspot.com/2008/02/getting-sqrs-to-publish-to-rds.html

In short the generated files must be created in the same folder that is generated for the process. I've found that App Engines at least are very fussy with filenames and they must follow the following convention:

AE_ProcessName_ProcessInstance.txt/csv

I have found, with App Engines at least, that the RDS only likes extensions txt and csv. Worth trying a few more.

The path can be found here:

SELECT PRCSOUTPUTDIR || '\'
FROM PSPRCSPARMS
WHERE PRCSINSTANCE = 11579


Example code:

Local string &pathNm
SQLExec("select prcsoutputdir || '\' from PSPRCSPARMS where prcsinstance = :1", UQ_UQAP0860_AET.PROCESS_INSTANCE.Value, &pathNm);

Local string &fileNm = "AE_UQAP0860_" | UQ_UQAP0860_AET.PROCESS_INSTANCE.Value | ".csv";

Note: I have noticed that the file does not publish to RDS/Report Manager if... well I'm not sure. Check that filename is not too long, does not contain extra periods (don't use %DateTime in the filename) etc., etc..