x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39


XML, SQL & DB2 V10

...or I guess it could be any platform.. Webcast/Conf Call on Monday...IBM Rep after 3 hours...I piped in... X002548: "ummm, excuse me, so are you saying that XML database models outperforms the relational model?" IBM: "In what regard?" X002548: "Data access and modifications?" IBM: "......crickets.....ummm no, the relational model is faster..."

posted @ Friday, October 08, 2010 3:41 PM | Feedback (0) | Filed Under [ DB2 Disconnected Ramblings ]

DB2 OS/390 Database COPY and RESTORE

Hey there!!!!! In the SQL Server world, if you want to create a database on a same server instance,  from an existing database, all you have to do  is something like: RESTORE DATABASE <db_name>    FROM DISK = '\\<file path>\<file name>'    WITH   MOVE '<logical data file name>_data' TO '\\<file path>\<file name>.MDF'         , MOVE '<logical log file name>_log'  TO '\\<file path>\<file name>.LDF'     , REPLACE GO In DB2?  OH NO!!!   SORRY!!! I guess you could use Platinum or some other tool, but that's like a mainframe wizard, and they always (wizards in general) are never 100% bullet proof. At least not for my liking. Anyway....first thing you gotta do...

posted @ Thursday, December 03, 2009 1:32 PM | Feedback (0) | Filed Under [ DB2 ]

sp_depends for DB2

Well, there really isn't anything that I know of that is like sp_depends for DB2 z/OS Version 7.2.  Hopefully V8 will alot more features...but for Now you have to interogate the catalog.  So this is how you do it....     SELECT DISTINCT NAME,DNAME,BNAME                       FROM SYSIBM.SYSPACKDEP D                       INNER JOIN SYSIBM.SYSPACKSTMT S                              ON D.DCOLLID = S.COLLID AND D.DNAME = S.NAME        AND D.DCONTOKEN = S.CONTOKEN                       WHERE BQUALIFIER = 'AXHRSPDA'                          AND BNAME IN('POSITION_TREE')                   ORDER BY NAME,DNAME,BNAME                          ;                                                    And this new editor for posting is pretty neat...lots of features...like background color...and for anyone not familiar with the mainframe...that's what I...

posted @ Wednesday, September 20, 2006 11:21 AM | Feedback (0) | Filed Under [ DB2 ]

How to FTP Files to OS/390

The best way I've found to transfer is to use ftp with a script file.  Now with Cyberfusion being implemented, I unfortunately have to abandon this simple methd for secure file transfers.  The thing that really gets me though, is this company policy being enforced, evenb though everything is interbal and behind a firewall.  And Cyberfusion iis a pain in the neck to setup...and it seems the "Service" company doesn't have a good handle on it...and they have to set up the profiles.  In any case, create a script file like: open <machine name> <UserID><Password>quote site filetype=seq  CYlinders Primary=50 Secondary=100 lrecl=5000 recfm=FBput...

posted @ Tuesday, August 01, 2006 11:20 AM | Feedback (0) | Filed Under [ DB2 ]

Generate History Tables in DB2 From the System Catalog

I'm currently setting up a process to audit changes to the database in DB2 OS/390.  I have done this in the past with SQL Server (which was easier) but I know have to do it for this platform.  Basically, for an UPDATE or a DELETE DML operation a trigger will fire and insert the rows into this table.  The following SQL will genertae table creates for each table in a database.  Just change the Creator for which ever database you want to audit.   SET CURRENT SQLID = 'BXHRSPDA'; SELECT SQL FROM ( SELECT NAME AS TABLE_NAME,1 AS ROWORDER, 1 AS COLORDER,...

posted @ Thursday, May 04, 2006 3:08 PM | Feedback (0) | Filed Under [ DB2 ]


Well there isn't one.  If you read this post and your platform is SQL Server or Oracle, I hope you feel very fortunate that you have TRUNCATE TABLE <tablename>.  It appears that in version 8, there is a callable procedure that is supplied by IBM, but it cannot be executed as a standard SQL command.  It probably not ANSI anyway.  In any event, the way of the world to do this in the past, to not incur heavy logging of a DELETE, was to use the DB2 Load utility and do a LOAD REPLACE.  This has the affect of marking...

posted @ Thursday, April 28, 2005 11:54 AM | Feedback (6) | Filed Under [ DB2 ]


No cursors please!  DB2 syntax varies from SQL Server. Just because it took me an hour to find it....I wish the syntax would be the same...but they're not  UPDATE ASSESS_AUDIT A1                                            SET FIRST_NAME = (SELECT SUB_FIRST_NAME                                        FROM ORG_TREE O1                                          WHERE A1.EMPL_ID = O1.EMPL_ID)   WHERE FIRST_NAME = ' '                                      AND EXISTS (SELECT * FROM ORG_TREE O2                                  WHERE A1.EMPL_ID = O2.EMPL_ID);           Another example UPDATE SS_FAMILY_TREE_MAP SFTM SET ORG_NAME = ( SELECT SUBSTR(SOR.SS_ORG_NAME,1,25) FROM SS_ORG_REL SOR WHERE SFTM.SS_ORG_CD = SOR.SS_ORG_CD AND SFTM.SS_STRUCTURE_NAME = SOR.STRUCTURE_NAME) WHERE ORG_NAME IS NULL OR ORG_NAME = ' ' ;

posted @ Wednesday, October 20, 2004 3:58 PM | Feedback (3) | Filed Under [ DB2 ]

Calculate the most effecient Storage size based on the catalog

The Following code is used to identify reccomended allocations, whether it be in Cylinders or tracks, and how much to allocate for a tablespace.   SELECT           NAME                                                                        ,  NACTIVE*PGSIZE                             AS KILOBYTES                     ,  PGSIZE                                     AS PAGE_SIZE                     ,  NACTIVE                                    AS PAGES                         ,  DECIMAL((NACTIVE/12.00),15,2)              AS TRACKS                        ,  DECIMAL(((NACTIVE/12.00)/15),15,2)         AS CYLINDERS                     ,  CASE                                                                             WHEN DECIMAL(((NACTIVE/12.00)/15),15,2) > 1 THEN                                    'CYLINDERS'                            ELSE                                    'TRACKS   '                                                               END                                      AS RECMNED_ALLOC                 ,  CASE                                                                             WHEN INTEGER(NACTIVE/12.00) <> NACTIVE/12.00 THEN                                  (INTEGER(NACTIVE/12)+1)*48               ELSE                                   INTEGER(NACTIVE/12)   *48                                                 END                                      AS FULL_TRACK_BYTES              ,  CASE                                                                             WHEN INTEGER(((NACTIVE/12.00)/15.00)) <>                                                     (NACTIVE/12.00)/15.00          THEN                           (INTEGER((NACTIVE/12.00)/15.00)+1)*720          ELSE          ...

posted @ Friday, January 30, 2004 2:01 PM | Feedback (3) | Filed Under [ DB2 ]

Powered by:
Powered By Subtext Powered By ASP.NET