Hi
Also conerting MSSQL to DB2(9.7),
We have problem...
I know ..
In the DB2(9.7), The MSSQL(sp_executesql) is possible to "execute
immediate"
But the following Query is possible??
The Logic is :
1) Create Temporary Table
2) Declare variable = 'alter table xxx'
3) Execute sp_executesql @variable

MSSQL)
DECLARE @docHandle INT,
@IntVariable INT,
@cCrTableSql NVARCHAR(MAX),
@cSchemaSql NVARCHAR(MAX),
@ParmDefinition NVARCHAR(500),
@ReplaceNm NVARCHAR(100),
@CommonCrTable NVARCHAR(1000),
@CommonHeader NVARCHAR(1000),
@pDataBlock NVARCHAR(100)

CREATE TABLE #TempTable (WorkingTag NCHAR(1) NULL)

SELECT @cCrTableSql = N'[CommonCrTable] alter table [DataBlock10] add
EmpSeq INT NULL alter table [DataBlock10] add SMAbrdName
NVARCHAR(100) NULL alter table [DataBlock10] add UMAbrdNationName
NVARCHAR(100) NULL alter table [DataBlock10] add TripArea
NVARCHAR(100) NULL alter table [DataBlock10] add TripRec
NVARCHAR(100) NULL alter table [DataBlock10] add DptDate NCHAR(8)
NULL alter table [DataBlock10] add ArvDate NCHAR(8) NULL ',
@cSchemaSql = N'INSERT INTO [DataBlock10]([CommonHeader],
EmpSeq, SMAbrdName, UMAbrdNationName, TripArea, TripRec, DptDate,
ArvDate) SELECT [CommonHeader], EmpSeq, SMAbrdName, UMAbrdNationName,
TripArea, TripRec, DptDate, ArvDate FROM OPENXML(@docHandle, N''/ROOT/
DataBlock10'', @xmlFlags) WITH [DataBlock10]'

SET @ReplaceNm = N'#TempTable'
SET @pDataBlock = N'DataBlock10'

SET @CommonCrTable = 'alter table '+'['+@pDataBlock+']' + ' add IDX_NO
INT NULL '
SET @CommonCrTable = @CommonCrTable + ' alter table '+'['+@pDataBlock
+']' + ' add DataSeq INT NULL '
SET @CommonCrTable = @CommonCrTable + ' alter table '+'['+@pDataBlock
+']' + ' add Selected INT NULL '
SET @CommonCrTable = @CommonCrTable + ' alter table '+'['+@pDataBlock
+']' + ' add MessageType INT NULL '
SET @CommonCrTable = @CommonCrTable + ' alter table '+'['+@pDataBlock
+']' + ' add Status INT NULL '
SET @CommonCrTable = @CommonCrTable + ' alter table '+'['+@pDataBlock
+']' + ' add Result NVARCHAR(255) NULL '
SET @CommonCrTable = @CommonCrTable + ' alter table '+'['+@pDataBlock
+']' + ' add ROW_IDX INT NULL '

SET @cCrTableSql = REPLACE(@cCrTableSql, '[CommonCrTable]',
@CommonCrTable)

SET @cCrTableSql = REPLACE(@cCrTableSql, '['+@pDataBlock+']',
@ReplaceNm)

SET @ParmDefinition = N'@docHandle int OUTPUT'

EXECUTE sp_executesql @cCrTableSql, @ParmDefinition,
@docHandle OUTPUT

SELECT * FROM #TempTable

DROP TABLE #TempTable

On 4/27/2010 8:00 AM, scv1977 wrote:
But in DB2 you want to execute each ALTER TABLE individually (don't
batch them up).

So:
EXECUTE IMMEDIATE 'ALTER TABLE ....' ;
EXECUTE IMMEDIATE '...';
and so forth



--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

Is possible EXECUTE IMMEDIATE 'ALTER TABLE GTE(global temp table)'
If not support, Are there other ways to handle "alter table GTE"