Systems Engineering and RDBMS

Archive for February 20th, 2007

Generating Export/Import commands for DB2 via SQL

Posted by decipherinfosys on February 20, 2007

Here are some simple SQL statements that can be used to generate out the Export and Import commands for moving the data from one schema to the other using export/import options in DB2 LUW.  This is just one of the many ways to do this.

–Export…change CURRENT SERVER to be your schema name
–Run this to generate those commands
–and then run them from a tool like Quest Central or Command Center to extract out the data

SET CURRENT SCHEMA = “WMSPROD”;
SET CURRENT PATH = “WMSPROD”,”SYSIBM”,”SYSFUN”,”SYSPROC”;

/****************************************************************************************************************
Export out the data from the Source Schema
You can change the CURRENT SERVER to be the name of the schema
*****************************************************************************************************************/

SELECT ‘EXPORT TO C:\DATA\’ || TABNAME || ‘.del ‘ || ‘ OF DEL SELECT * FROM  ‘ || CURRENT SERVER || ‘.’ || TABNAME || ‘;’
FROM SYSCAT.TABLES
WHERE TABSCHEMA = CURRENT SERVER
AND TYPE = ‘T’
ORDER BY TABNAME;

/****************************************************************************************************************
Import…change CURRENT SERVER to be the name that you create here for the schema
For tables that do not have identity columns.
*****************************************************************************************************************/

SELECT ‘IMPORT FROM C:\DATA\’ || A.TABNAME || ‘.del ‘ || ‘ OF DEL MODIFIED BY COMPOUND=5 COMMITCOUNT 1000 INSERT INTO ‘ || CURRENT SERVER || ‘.’ || A.TABNAME || ‘;’
FROM SYSCAT.TABLES A
WHERE A.TABSCHEMA = CURRENT SERVER
AND A.TYPE = ‘T’
AND NOT EXISTS (SELECT 1 FROM SYSCAT.COLUMNS B WHERE B.TABNAME = A.TABNAME AND B.IDENTITY = ‘Y’)
ORDER BY A.TABNAME;

/****************************************************************************************************************
Import…for tables that have identity columns.
*****************************************************************************************************************/

SELECT ‘IMPORT FROM C:\DATA\’ || A.TABNAME || ‘.del ‘ || ‘ OF DEL COMMITCOUNT 1000 INSERT INTO ‘ || CURRENT SERVER || ‘.’ || A.TABNAME || ‘;’
FROM SYSCAT.TABLES A
WHERE A.TABSCHEMA = CURRENT SERVER
AND A.TYPE = ‘T’
AND EXISTS (SELECT 1 FROM SYSCAT.COLUMNS B WHERE B.TABNAME = A.TABNAME AND B.IDENTITY = ‘Y’)
ORDER BY A.TABNAME;

Posted in DB2 LUW | 1 Comment »

SP2 for SQL Server 2005 is released

Posted by decipherinfosys on February 20, 2007

The second service pack for SQL Server 2005 was released yesterday by Microsoft. There are a lot of very good fixes in this one. We had faced a major issue in the SP1 version where the CPU utilization went to 100% when the system was put under heavy load and it was related to query parameterization issues and parallel query plan generations when the optimzier should have been using a serial plan. The hack was to use the MAXDOP hint or to set the max degree of parallelism to be less than the number of processors on the system (we set it to 1 in order to avoid the issue) – since all our queries in the system were small in nature, a serial execution plan was much more favorable. In addition, the parameterization was changed to forced and that way we got around this issue in SP1. However, this issue is fixed in SP2. Here are the links:

Main SP2 Page: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

Download SP2: http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en

KB 921896 (the fix list): http://support.microsoft.com/?kbid=921896

As always, please apply these on the test system(s) and do regression and benchmark tests of your applications prior to rolling it out in production.

Posted in SQL Server | 1 Comment »

TNS-12542 error on Windows Client machines

Posted by decipherinfosys on February 20, 2007

TNS-12542 error states: “TNS: Address already in use” and this is accompanied by TNS-00512 (Address in use) and TNS-12560 (generic protocol adapter error).

You can get this error either when you are executing large batch jobs or if you are under heavy load in a highly transactional OLTP system.  If you are getting this error in the client trace files in your Windows client environment, the problem would be due to the fact that the free ports in the Windows client has been exhausted and it is trying to make use of a port which is in TIME_WAIT state and thus it results into the TNS-12542 error.

There are two options that you have in order to resolve this issue:

1)  You can increase the free port range.  You can do this by editing the registry.  Using regedt32.exe, locate this registry entry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

On the Edit menu, click Add Value, and then add the following registry value:

Value Name: MaxUserPort Data Type: REG_DWORD Value: 65534

Valid Range: 5000-65534 (decimal)

Default: 0x1388 (5000 decimal)

This parameter controls the maximum port number used when an application requests any available user port from the system.

2) The other option is to decrease the value for TIME_WAIT state:

TcpTimedWaitDelay

Key: Tcpip\Parameters

Value Type: REG_DWORD – Time in seconds

Valid Range: 30-300 (decimal)

Default: 0xF0 (240 decimal)

This parameter determines the length of time that a connection will stay in the TIME_WAIT state when being closed. While a connection is in the TIME_WAIT state, the socket pair cannot be re- used.

Posted in Oracle | Leave a Comment »