DB2
...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..."
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...
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...
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...
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,...
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...
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 = ' '
;
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 ...