How to create a temporary DB2 table

The problem that DB2 temporary tables cannot be created is indeed a headache. The following describes how to solve the problem that DB2 temporary tables cannot be created. We hope this will help you learn about DB2 temporary tables.

 
 

  
  
Procedure TXXXForm. lc_procHandleStandard (strpo: string; xPOSEQ: Integer); // HXYADD----24/09/2010 ----
  
  
Var
  
  
StrFieldList: string; // A-JW-02-351101-01
  
  
Begin
  
  
StrFieldList: = 'xname VARCHAR (80), '#13 #10
  
  
+ 'Xkind VARCHAR (20), '#13 #10
  
  
+ 'Ename VARCHAR (20), '#13 #10
  
  
+ 'Cname VARCHAR (20), '#13 #10
  
  
+ 'Xbiao VARCHAR (80), '#13 #10
  
  
+ 'Xcode INTEGER, '#13 #10
  
  
+ 'Xgrade INTEGER ';
  
  
StrTmpTableName: = lc_CreatTmpTable (strFieldList );
  
  
If strTmpTableName = ''then
  
  
Begin
  
  
Messagebox (handle, 'unable to create a [Execute standard materials] temporary table! ', 'prompt information', mb_ OK );
  
  
Exit;
  
  
End ;//*)
  
  
Qry6.Close;
  
  
Qry6. SQL. Clear;
  
  
Qry6. SQL. Add ('delete from' + strTmpTableName );
  
  
Qry6.ExecSQL;
  
  
 
  
  
Qry6.Close;
  
  
Qry6. SQL. Clear;
  
  
Qry6. SQL. Add ('insert' + strTmpTableName );
  
  
Qry6. SQL. Add ('(XNAME, XKIND, ENAME, CNAME, XBIAO, XCODE, XGRADE )');
  
  
Qry6. SQL. Add ('select VALUE (K. PROD_NAME ,''''),');
  
  
Qry6. SQL. Add ('case K. PROD_KIND WHEN 0 then'' '0 primary color production ''');
  
  
Qry6. SQL. Add ('when 1 then' '1 washing product ''');
  
  
Qry6. SQL. Add ('else' X does not classify ''end PROD_KIND ,');
  
  
Qry6. SQL. Add ('value (A. COLOR_ENG_NAME ,''''),');
  
  
Qry6. SQL. Add ('value (A. COLOR_CHN_NAME ,''''),');
  
  
Qry6. SQL. Add ('value (K. ZHIXIN_BIAO ,''''),');
  
  
Qry6. SQL. Add ('k.s _ CODE ,');
  
  
Qry6. SQL. Add ('A. COLOR_GRADE ');
  
  
Qry6. SQL. Add ('from GSSAL. SAL_PO_DTLA ');
  
  
Qry6. SQL. Add ('left JOIN GSSAL. SAL_PO X ON (X. PO_NO = A. PO_NO AND X. GEN_POSEQ = A. GEN_POSEQ )');
  
  
Qry6. SQL. Add ('left JOIN GSSAL. SAL_KIND K ON (K.S _ CODE = VALUE (A. PROD_CODE, X. PROD_CODE ))');
  
  
Qry6. SQL. Add ('where A. PO_NO =: PO_NO AND A. GEN_POSEQ =: GEN_POSEQ ');
  
  
Qry6. SQL. Add ('AND VALUE (K. ISVALID, ''n'') = ''y ''');
  
  
Qry6. SQL. Add ('order BY K. PROD_NAME, A. COLOR_ENG_NAME ');
  
  
Qry6.ParamByName ('po _ no'). AsString: = strpo;
  
  
Qry6.ParamByName ('gen _ POSEQ '). AsInteger: = xPOSEQ;
  
  
Qry6.ExecSQL;
  
  
Qry6.Close;
  
  
Qry6. SQL. Clear;
  
  
Qry6. SQL. Add ('select *');
  
  
Qry6. SQL. Add ('from' + strTmpTableName );
  
  
Qry6.Open;
  
  
End;
  
  
Certificate -----------------------------------------------------------------------------------------------------------------------------------
  
  
Function TXXXForm. lc_CreatTmpTable (strFieldList: string): string;
  
  
Var
  
  
StrTableName: string;
  
  
BlnSuccess: boolean;
  
  
I: integer;
  
  
Function lc_funcCreatTable (strFieldList, strTableName: string): Boolean;
  
  
Var
  
  
QryCreatTemp: TQuery;
  
  
Begin
  
  
Result: = False;
  
  
QryCreatTemp: = TQuery. Create (self );
  
  
With qryCreatTemp do
  
  
Begin
  
  
DatabaseName: = 'gscomdb ';
  
  
SQL. Clear;
  
  
SQL. Add ('create table' + strTableName + '(' + strFieldList + ') IN USERSPACE1 ');
  
  
Try
  
  
ExecSQL;
  
  
Result: = True;
  
  
Except
  
  
End;
  
  
End;
  
  
QryCreatTemp. Free;
  
  
End;
  
  
Begin
  
  
Result: = ''; // If creation fails, an empty string is returned.
  
  
StrTableName: = 'maid. TMP _ '+ FormatFloat ('0', now * 10000000) + '';
  
  
For I: = 1 to 50 do // loop for 50 times, and then exit if it cannot be created.
  
  
Begin
  
  
BlnSuccess: = lc_funcCreatTable (strFieldList, strTableName );
  
  
If blnSuccess then // if the table is successfully created, the table name is returned and the table exits.
  
  
Begin
  
  
Result: = strTableName;
  
  
Exit;
  
  
End;
  
  
End;
  
  
End;
  
  
Certificate -----------------------------------------------------------------------------------------------------------------------------------
  
  
Procedure TXXXForm. FormCloseQuery (Sender: TObject; var CanClose: Boolean );
  
  
Begin
  
  
LstUser. Free;
  
  
If strTmpTableName <> ''then
  
  
Begin
  
  
Lc_DeleteTable (strTmpTableName );
  
  
StrTmpTableName: = '';
  
  
End;
  
  
End;
  
  
Certificate -----------------------------------------------------------------------------------------------------------------------------------
  
  
Function TXXXForm. lc_DeleteTable (strTableName: string): Boolean;
  
  
Var
  
  
QryDeleteTemp: TQuery;
  
  
Begin
  
  
Result: = False;
  
  
QryDeleteTemp: = TQuery. Create (Self );
  
  
With qryDeleteTemp do
  
  
Begin
  
  
DatabaseName: = 'gscomdb ';
  
  
SQL. Clear;
  
  
SQL. Add ('drop table' + strTableName );
  
  
Try
  
  
ExecSQL;
  
  
Result: = True;
  
  
Except
  
  
End;
  
  
End;
  
  
QryDeleteTemp. Free;
  
  
End;
  
  
Certificate -----------------------------------------------------------------------------------------------------------------------------------
 
 

Learn about the DB2 lock types

Solution to db2 tablespace lock

Implementation of adding verification constraints to DB2

How to Create a tablespace in DB2

Introduction to the DB2 user group in Linux