SELECT DISTINCT ltrim (rtrim (a.classid)) AS classid, ltrim (rtrim (b.menuname)) AS menuname, ltrim (rtrim (b.barname)) AS barname, ltrim (rtrim (b.baritemname)) AS baritemname, ltrim(rtrim(d.pnlname)) AS pnlname, ltrim (rtrim (d.itemlabel)) AS itemlabel, ltrim (rtrim (e.itemlabel)) AS itemlabel, ltrim(rtrim(c.pageaccessdescr)) AS pageaccessdescr, b.displayonly, ltrim (rtrim (f.portal_label)) AS portal_label, ltrim (rtrim (f.portal_objname)) AS portal_objname, ltrim(rtrim(f.portal_uri_seg1)) AS portal_uri_seg1, ltrim (rtrim (f.portal_uri_seg2) ) AS portal_uri_seg2, (SELECTCASE WHEN ltrim(rtrim(descr)) = ''THEN ltrim(rtrim(menulabel))ELSE ltrim(rtrim(descr))ENDFROM psmenudefnWHERE menuname = b.menuname) || ' > ' || ltrim(rtrim(f.portal_label)) AS pathFROM psroleclass a, psauthitem b, pspgeaccessdesc c, pspnlgroup d, psmenuitem e, psprsmdefn f, psprsmperm gWHERE a.classid = b.classidAND d.pnlgrpname = e.pnlgrpnameAND b.menuname = e.menunameAND b.barname = e.barnameAND b.baritemname = e.itemname--AND a.rolename LIKE 'GLIC_PO%'AND b.menuname NOT IN ('APPLICATION_DESIGNER', 'CLIENTPROCESS', 'DATA_MOVER ', 'IMPORT_MANAGER ', 'OBJECT_SECURITY ', 'QUERY ', 'PERFMONPPMI ')AND b.menuname NOT LIKE 'WEBLIB%' AND B.PNLITEMNAME IN (SELECT DISTINCT PNLNAMEFROM PSFSSYS.PSPNLFIELDwhere RECNAME ='PYMNT_ADVICE')AND NOT EXISTS (SELECT 'x'FROM psprsmsysattrvlWHERE portal_name = f.portal_nameAND portal_reftype = f.portal_reftypeAND portal_objname = f.portal_objnameAND portal_attr_nam = 'PORTAL_HIDE_FROM_NAV'AND f.portal_objname NOT IN ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA'))AND b.authorizedactions = c.authorizedactionsAND f.portal_reftype = 'C'AND f.portal_cref_usgt = 'TARG'AND f.portal_name = g.portal_nameAND f.portal_reftype = g.portal_reftypeAND f.portal_objname = g.portal_objnameAND a.classid = g.portal_permnameAND b.classid = g.portal_permnameAND f.portal_name = 'EMPLOYEE'AND f.portal_uri_seg1 <> ' 'AND f.portal_uri_seg2 <> ' 'AND f.portal_uri_seg3 <> ' 'AND f.portal_uri_seg1 = b.menunameAND b.pnlitemname = d.itemname WITH UR;
SQLs below have proved to be great while working behind the scenes and creating report about PeopleSoft customizations. These are quick ways to get behind the scenes and see the objects the way PeopleSoft sees it.
Getting fields and records for a page:
select OCCURSLEVEL as level ,FIELDNUM as taborder, LBLTEXT as label , RECNAME as record ,FIELDNAME as field from PSFSSYS.PSPNLFIELD where PNLNAME ='PORTAL_HPCOMP'order by OCCURSLEVEL, FIELDNUM
Get information about Project Compares :
SELECT PROJECTNAME, VERSION, PROJECTDESCR, TGTSERVERNAME, TGTDBNAME, TGTOPRID, TGTOPRACCT, COMPRELEASE, SRCCOMPRELDTTM, TGTCOMPRELDTTM, COMPRELDTTM, KEEPTGT, TGTORIENTATION, COMPARETYPE, COMMITLIMIT, REPORTFILTER, MAINTPROJ, LASTUPDDTTM, LASTUPDOPRID, RELEASELABEL, RELEASEDTTM, OBJECTOWNERID, DESCRLONG FROM PSPROJECTDEFN
Get Record Audit Flags:
SELECT RECNAME, FIELDNUM, FIELDNAME, INTEGER(USEEDIT / 8) - INTEGER (USEEDIT / 16) * 2 as "ADD", INTEGER(USEEDIT / 128) - INTEGER(USEEDIT / 256)*2 as "CHANGE", INTEGER(USEEDIT / 1024) - INTEGER(USEEDIT/ 2048)*2 as "DELETE" FROM PSRECFIELD WHERE RECNAME = 'PSOPRDEFN' and USEEDIT <> 0 AND (INTEGER (USEEDIT / 128) - INTEGER (USEEDIT / 256) * 2 <> 0 OR INTEGER (USEEDIT / 8) - INTEGER (USEEDIT / 16) * 2 <> 0 OR INTEGER (USEEDIT / 1024) - INTEGER (USEEDIT / 2048) * 2 <> 0) ORDER BY RECNAME, FIELDNUM;
Get Record Field Types and Definitions:
-- GET RECORD NAME DEFINITION SELECT A.RECNAME, A.FIELDNAME, CASE WHEN B.FIELDTYPE = 0 THEN 'CHAR' WHEN B.FIELDTYPE = 1 THEN 'LONG CHAR' WHEN B.FIELDTYPE = 2 THEN 'NUMBER' WHEN B.FIELDTYPE = 3 THEN 'SIGNED NBR' WHEN B.FIELDTYPE = 4 THEN 'DATE' WHEN B.FIELDTYPE = 5 THEN 'TIME' WHEN B.FIELDTYPE = 6 THEN 'DATETIME' WHEN B.FIELDTYPE = 7 OR B.FIELDTYPE = 8 THEN 'IMAGE' ELSE NULL END AS FIELDTYPE, CASE WHEN B.FIELDTYPE = 2 OR B.FIELDTYPE = 3 THEN RTRIM (CHAR (B.LENGTH))|| '.'||CHAR (B.DECIMALPOS) ELSE CHAR (B.LENGTH) END AS FLDLEN, CASE WHEN (A.USEEDIT/ 256)/ 2 <> (A.USEEDIT/ 256)/ 2.0 THEN 'YES' ELSE 'NO' END AS REQ, CASE WHEN A.USEEDIT/ 2 <> A.USEEDIT/ 2.0 THEN 'KEY' WHEN (A.USEEDIT/ 2)/ 2 <> (A.USEEDIT/ 2)/ 2.0 THEN 'DUP' WHEN (A.USEEDIT/ 16)/ 2 <>(A.USEEDIT/16)/ 2.0 THEN 'ALT' ELSE NULL END AS KEY_TYPE, CASE WHEN (A.USEEDIT / 64) / 2 <> (A.USEEDIT/64)/2.0 THEN 'DESC' WHEN (A.USEEDIT / 2 <> A.USEEDIT / 2.0 OR (A.USEEDIT / 2) / 2 <> (A.USEEDIT / 2) / 2.0 OR (A.USEEDIT / 16) / 2 <> (A.USEEDIT / 16) / 2.0) AND (A.USEEDIT / 64) / 2 = (A.USEEDIT / 64) / 2.0 THEN 'ASC' ELSE NULL END AS DIR, CASE WHEN (A.USEEDIT / 2048) / 2 <> (A.USEEDIT / 2048) / 2.0 THEN 'YES' ELSE 'NO' END AS SRCH, CASE WHEN (A.USEEDIT / 32) / 2 <> (A.USEEDIT / 32) / 2.0 THEN 'YES' ELSE 'NO' END AS LIST, CASE WHEN (A.USEEDIT / 4)/ 2 <>(A.USEEDIT / 4)/ 2.0 THEN 'YES' ELSE 'NO' END AS SYS, CASE WHEN A.DEFRECNAME = '' THEN A.DEFFIELDNAME ELSE RTRIM(A.DEFRECNAME)|| '.'|| A.DEFFIELDNAME END AS DEFAULT_VALUE, CASE WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0 AND (A.USEEDIT / 128) / 2 = (A.USEEDIT / 128) / 2.0 AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0 THEN 'A' WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0 AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0 AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0 THEN 'AC' WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0 AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0 AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0 THEN 'ACD' WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0 AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0 AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0 THEN 'C' WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0 AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0 AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0 THEN 'CD' WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0 AND (A.USEEDIT / 128) / 2 = (A.USEEDIT / 128) / 2.0 AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0 THEN 'D' ELSE NULL END AS AUDT, CASE WHEN (A.USEEDIT / 16384) / 2 <> (A.USEEDIT / 16384) / 2.0 THEN 'PROMPT' WHEN (A.USEEDIT / 512) / 2 <> (A.USEEDIT / 512) / 2.0 THEN 'XLAT' WHEN (A.USEEDIT / 8192) / 2 <> (A.USEEDIT / 8192) / 2.0 THEN 'Y/N' ELSE NULL END AS EDIT, A.EDITTABLE AS PROMPT_TABLE, A.SETCNTRLFLD AS SET_CONTROL_FLD, CASE WHEN (A.USEEDIT / 4096) / 2 <> (A.USEEDIT / 4096) / 2.0 THEN 'YES' ELSE 'NO' END AS REASONABLE_DT, CASE WHEN (A.USEEDIT / 32768) / 2 <> (A.USEEDIT / 32768) / 2.0 THEN 'YES' ELSE 'NO' END AS AUTO_UPDT, CASE WHEN (A.USEEDIT / 262144) / 2 <> (A.USEEDIT / 262144) / 2.0 THEN 'FROM' WHEN (A.USEEDIT / 524288) / 2 <> (A.USEEDIT / 524288) / 2.0 THEN 'THROUGH' ELSE NULL END AS SEARCH_FIELD, CASE WHEN A.SUBRECORD = 'Y' THEN 'YES' ELSE 'NO' END AS SUBRECORD, A.LASTUPDDTTM, A.LASTUPDOPRID FROM PSRECFIELD A, PSDBFIELD B WHERE A.RECNAME = 'PSOPRDEFN' AND A.FIELDNAME = B.FIELDNAMEORDER BY FIELDNUM
Get Field Translate Values:
SELECT fieldvalue, eff_status, xlatlongname FROM psxlatitem i WHERE fieldname = 'RUNSTATUS' AND effdt = ( SELECT max(effdt) FROM psxlatitem i1 WHERE i1.fieldname = i.fieldname AND i1.fieldvalue = i.fieldvalue AND i1.effdt <= CURRENT DATE) ORDER BY INTEGER(fieldvalue)
Get information about PS Queries:
-- Get Query DefinitionSELECT OPRID, QRYNAME, DESCR, VERSION, LASTUPDDTTM, LASTUPDOPRID, QRYAPPROVED, CREATEOPRID, CREATEDTTM, QRYDISABLED, QRYFOLDER, DESCRLONG FROM PSFSSYS.PSQRYDEFN where QRYNAME = '?' and oprid = '?'with ur; -- Get Query Bind VariablesSELECT OPRID, QRYNAME, BNDNAME, BNDNUM, FIELDNAME, HDGTYPE, HEADING, FIELDTYPE , LENGTH, DECIMALPOS, FORMAT, EDITTABLE, USECOUNT, USEEDIT FROM PSFSSYS.PSQRYBIND where QRYNAME like '?%' and oprid = '?' with ur; -- Get Query CriteriaSELECT OPRID, QRYNAME, SELNUM, CRTNUM, COMBTYPE, NEGATION, LPARENLVL, LCRTSELNUM, LCRTFLDNUM, CONDTYPE, EXPRTYPE, R1CRTSELNUM, R1CRTFLDNUM, R1CRTEXPNUM, R2CRTSELNUM, R2CRTFLDNUM, R2CRTEXPNUM, RPARENLVL, QRYOJSELNUM FROM PSFSSYS.PSQRYCRITERIA where QRYNAME = '?' and oprid = '?'with ur; -- Get Query Output Field DefinitionsSELECT OPRID, QRYNAME, QRYFLDNAME, SELNUM, FLDNUM, RECNAME, FIELDNAME, FLDRCDNUM, FLDEXPNUM, HDGTYPE, HEADING, COLUMNNUM, GROUPBYNUM, ORDERBYNUM, ORDERBYDIR, TTLTYPE, SUBTTLNUM, USECOUNT, XLATTYPE, XLATEXPRTYPE, XLATFLDNUM, XLATEXPNUM, AGGREGATEFUNC FROM PSFSSYS.PSQRYFIELD where QRYNAME like '?%' with ur;
If you have more information about this kind of handy SQLs – please comment and I will try and keep this post up to date.
Mar 192008
Need to delete millions of rows from a table fast? Here is how you can do this:
1: ALTER TABLE table_name ACTIVATE NOT LOGGED INITIALLY; --disable logging
2: DELETE FROM table_name WHERE ....;
3: COMMIT; --Commits and enable logging
Some of the processes that depend on shared table instances , can experience a dramatic differences in performance, in case all of the instances available are being used, or remaining locked by some orphan or failed process. One way to resolve this problem is to go online and check if tables should be “released”. Here is a set of SQLs that would allow you to do the same thing and depending on you requirements – allow to automate this process, which could prove critical during the times of high usage:
-- Shared Temp Table instance control tableSELECT * FROM PS_TEMP_TBL_ASGNMWHERE TEMP_TBL_NAME = 'table_name'WITH UR -- Get tables that were locked by processes that ran 200 instance numbers beforeSELECT * FROM PS_TEMP_TBL_ASGNMWHERE in_use_sw = 'Y' AND process_instance < (SELECT max (prcsinstance) - 200 FROM psfssys.psprcsrqst WHERE runstatus = '9')with ur -- RELEASE ALL TEMP TABLE LOCKS -- FROM PROCESS INSTANCES THAT ARE LESS THAN 200 THAN THE LAST SUCCESSFUL ONEUPDATE ps_temp_tbl_asgnm SET in_use_sw = 'N', PROCESS_INSTANCE = 0WHERE in_use_sw = 'Y' AND process_instance < (SELECT max (prcsinstance) - 200 FROM psfssys.psprcsrqst WHERE runstatus = '9')
Let me know if you have any questions
Mar 042008
Here are a few more nice DB2 scripts that would save you time:
Get all tables in Table Space
-- Get all tables in particular tablespaceselect SUBSTR(TABNAME,1,25) "TABNAME", SUBSTR(TABSCHEMA,1,15) "SCHEMA", NPAGES, CARD from syscat.tables where tbspace = '?'
Generate Grants for all tables in Table Space
-- Genereating GRANTS SELECT 'grant select on ' || tabname || ' to PSFDEV' || ';' FROM syscat.tables WHERE tabname NOT IN (SELECT tabname FROM syscat.tabauth WHERE grantee = 'PSFDEV') AND tabschema = 'PSFSSYS' AND (type = 'T' OR type = 'V')
Oracle PeopleTools Blog
Recent Comments