WorkingScripts

The Stuff that Just Works

WorkingScripts header image 5

Entries Tagged as 'SQL'

SQL for Retrieving Portal, Security, Record and Navigation

June 5th, 2009 No Comments

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

Tags:   · ·

Getting Information about PeopleSoft Components from Database

June 3rd, 2008 No Comments

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

Tags:   · ·

Deleting a lot of rows fast

March 19th, 2008 No Comments

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

Tags:   ·

Non-Shared Temp Tables Cleanup

March 7th, 2008 No Comments

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

Tags:   · · ·

DB2 Scripts – Table Management

March 4th, 2008 No Comments

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

Tags:   · ·