/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: tbtemp.sqC
**
** SAMPLE: How to use a declared temporary table
**
** This sample:
** 1. Creates a user temporary table space required for declared
** temporary tables
** 2. Creates and populates a declared temporary table
** 3. Shows that the declared temporary table exists after a commit
** and shows the declared temporary table's use in a procedure
** 4. Shows that the temporary table can be recreated with the same
** name using the "with replace" option and without "not logged"
** clause, to enable logging.
** 5. Shows the creation of an index on the temporary table.
** 6. Show the usage of "describe" command to obtain information
** regarding the tempraroy table.
** 7. Shows the usage of db2RunStats API to to update statistics
** about the physical characteristics of a temp table and the
** associated indexes.
** 8. Shows that the temporary table is implicitly dropped with a
** disconnect from the database
** 9. Drops the user temporary table space
**
** The following objects are made and later removed:
** 1. a user temporary table space named usertemp1
** 2. a declared global temporary table named temptb1
** (If objects with these names already exist, an error message
** will be printed out.)
**
** SQL STATEMENTS USED:
** CLOSE
** CREATE USER TEMPORARY TABLESPACE
** CREATE INDEX
** DECLARE CURSOR
** DECLARE GLOBAL TEMPORARY TABLE
** DESCRIBE
** DROP TABLESPACE
** FETCH
** INCLUDE
** INSERT
** OPEN
** PREPARE
**
** DB2 APIs USED:
** db2RunStats
**
** STRUCTURES USED:
** sqlca
** sqlda
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <stdio.h>
#include <string.h>
#include <malloc.h>
#include <sqlenv.h>
#include <sqlcodes.h>
#include <sqlutil.h>
#include <sqlda.h>
#include <db2ApiDf.h>
#include "utilemb.h"
#if ((__cplusplus >= 199711L) && !defined DB2HP && !defined DB2AIX) || \
(DB2LINUX && (__LP64__ || (__GNUC__ >= 3)) )
#include <iomanip>
#include <iostream>
using namespace std;
#else
#include <iomanip.h>
#include <iostream.h>
#endif
// Prototypes:
// main
// |_CreateTablespace
// |_DeclareTempTable
// | |_PopulateTempTable
// | |_ShowTableContents
// |_ShowAfterCommit
// | |_ShowTableContents
// |_RecreateTempTableWithLogging
// | |_PopulateTempTable
// | |_ShowTableContents
// |_CreateIndex
// |_UpdateStatistics
// |_DescribeTemporaryTable
// | |_NumToAscii
// |_DropTablespace
EXEC SQL INCLUDE SQLCA;
// Variables to store data from the department table
EXEC SQL BEGIN DECLARE SECTION;
char deptno[4];
char deptname[30];
char mgrno[7];
short mgrnoInd; // use an indicator because null values are possible
char admrdept[4];
char location[17];
short locationInd; // use an indicator because null values are possible
char strStmt[100];
EXEC SQL END DECLARE SECTION;
class TbTemp
{
public:
int CreateTablespace(void);
int DeclareTempTable(void);
int ShowAfterCommit(void);
int DropTablespace(void);
int RecreateTempTableWithLogging(void);
int CreateIndex(void);
int UpdateStatistics(void);
int DescribeTemporaryTable(void);
private:
int PopulateTempTable(void);
int ShowTableContents(void);
char *NumToAscii(int);
};
// Create a user temporary table space for the temporary table. A user
// temporary table space is required for temporary tables. This type of
// table space is not created at database creation time.
int TbTemp::CreateTablespace(void)
{
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENT:" << endl;
cout << " CREATE USER TEMPORARY TABLESPACE " << endl;
cout << "TO MAKE A USER TEMPORARY TABLESPACE FOR THE TEMPORARY TABLE\n";
cout << "IN A DIRECTORY CALLED usertemp, RELATIVE TO THE DATABASE\n";
cout << "\n Perform:" << endl;
cout << " EXEC SQL CREATE USER TEMPORARY TABLESPACE usertemp1\n";
cout << " MANAGED BY SYSTEM " << endl;
cout << " USING ('usertemp')" << endl;
EXEC SQL CREATE USER TEMPORARY TABLESPACE usertemp1
MANAGED BY SYSTEM
USING ('usertemp');
EMB_SQL_CHECK("create user temporary tablespace");
return sqlca.sqlcode;
} // TbTemp::CreateTablespace
// Declare a temporary table with the same columns as the one for the
// database's department table. Populate the temporary table and
// show the contents.
int TbTemp::DeclareTempTable(void)
{
int rc = 0;
// Declare the declared temporary table. It is created empty.
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENT:" << endl;
cout << " DECLARE GLOBAL TEMPORARY TABLE" << endl;
cout << "TO MAKE A GLOBAL DECLARED TEMPORARY TABLE WITH THE SAME" << endl;
cout << "COLUMNS AS THE DEPARTMENT TABLE." << endl;
cout << "\n Perform:" << endl;
cout << " EXEC SQL DECLARE GLOBAL TEMPORARY TABLE temptb1 " << endl;
cout << " LIKE department " << endl;
cout << " NOT LOGGED" << endl;
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE temptb1
LIKE department
NOT LOGGED;
EMB_SQL_CHECK("declare global temporary table");
rc = sqlca.sqlcode;
if (rc != 0)
{
return rc;
}
rc = PopulateTempTable();
if (rc != 0)
{
return rc;
}
rc = ShowTableContents();
return rc;
} // TbTemp::DeclareTempTable
// Show that the temporary table still exists after the commit. All the
// rows will be deleted because the temporary table was declared, by default,
// with "on commit delete rows". If "on commit preserve rows" was used,
// then the rows would have remained.
int TbTemp::ShowAfterCommit(void)
{
int rc = 0;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENT:" << endl;
cout << " COMMIT" << endl;
cout << "TO SHOW THAT THE TEMP TABLE EXISTS AFTER A COMMIT BUT WITH\n";
cout << "ALL ROWS DELETED" << endl;
cout << "\n Perform:" << endl;
cout << " EXEC SQL COMMIT" << endl;
EXEC SQL COMMIT;
EMB_SQL_CHECK("commit");
rc = sqlca.sqlcode;
if (rc != 0)
{
return rc;
}
rc = ShowTableContents();
return rc;
} // TbTemp::ShowAfterCommit
// Declare the temp table temptb1 again this time with logging option,
// thereby replacing the existing one. If the "with replace" option is
// not used, then an error will result if the table name is already
// associated with an existing temporary table. Populate and show the
// contents again.
int TbTemp::RecreateTempTableWithLogging(void)
{
int rc = 0;
// Declare the declared temporary table again, this time without the
// NOT LOGGED clause. It is created empty.
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENT:" << endl;
cout << " DECLARE GLOBAL TEMPORARY TABLE" << endl;
cout << "TO REPLACE A GLOBAL DECLARED TEMPORARY TABLE WITH A NEW" << endl;
cout << "TEMPORARY TABLE OF THE SAME NAME WITH LOGGING ENABLED." << endl;
cout << "\n Perform:" << endl;
cout << " EXEC SQL DECLARE GLOBAL TEMPORARY TABLE temptb1 " << endl;
cout << " LIKE department " << endl;
cout << " WITH REPLACE" << endl;
cout << " ON COMMIT PRESERVE ROWS" << endl;
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE temptb1
LIKE department
WITH REPLACE
ON COMMIT PRESERVE ROWS;
EMB_SQL_CHECK("declare global temporary table with replace");
rc = sqlca.sqlcode;
if (rc != 0)
{
return rc;
}
rc = PopulateTempTable();
if (rc != 0)
{
return rc;
}
rc = ShowTableContents();
return sqlca.sqlcode;
} // TbTemp::RecreateTempTableWithLogging
// Create Index command can be used on temporary tables to improve
// the performance of queries
int TbTemp::CreateIndex(void)
{
cout << "\n-----------------------------------------------------------";
cout << "\n Indexes can be created for temporary tables. Indexing a table\n";
cout << " optimizes query performance " << endl;
cout << "\n CREATE INDEX session.tb1ind " << endl;
cout << " ON session.temptb1 (deptno DESC) " << endl;
cout << " DISALLOW REVERSE SCANS " << endl;
EXEC SQL CREATE INDEX session.tb1ind
ON session.temptb1(deptno DESC)
DISALLOW REVERSE SCANS;
cout << "\n Following clauses in create index are not supported " << endl;
cout << " for temporary tables:" << endl;
cout << " SPECIFICATION ONLY" << endl;
cout << " CLUSTER" << endl;
cout << " EXTEND USING" << endl;
cout << " Option SHRLEVEL will have no effect when creating indexes " << endl;
cout << " on DGTTs and will be ignored " << endl;
cout << "\n Indexes can be dropped by issuing DROP INDEX statement, " << endl;
cout << " or they will be implicitly dropped when the underlying temp " << endl;
cout << " table is dropped." << endl;
return sqlca.sqlcode;
} // TbTemp::CreateIndex
// db2RunStats updates statistics about the characteristics of the temp
// table and/or any associated indexes. These characteristics include,
// among many others, number of records, number of pages, and average
// record length.
int TbTemp::UpdateStatistics(void)
{
struct sqlca sqlca;
char fullTableName[258];
db2Uint32 versionNumber = db2Version970;
db2RunstatsData runStatData;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE DB2 API:" << endl;
cout << " db2Runstats -- Runstats" << endl;
cout << "TO UPDATE session.temptb1 STATISTICS." << endl;
strcpy(fullTableName, "session.temptb1");
// runstats table
runStatData.iSamplingOption = 0;
runStatData.piTablename = ( unsigned char *) fullTableName;
runStatData.piColumnList = NULL;
runStatData.piColumnDistributionList = NULL;
runStatData.piColumnGroupList = NULL;
runStatData.piIndexList = NULL;
runStatData.iRunstatsFlags = DB2RUNSTATS_ALL_INDEXES;
runStatData.iNumColumns = 0;
runStatData.iNumColdist = 0;
runStatData.iNumColGroups = 0;
runStatData.iNumIndexes = 0;
runStatData.iParallelismOption = 0;
runStatData.iTableDefaultFreqValues = 0;
runStatData.iTableDefaultQuantiles = 0;
runStatData.iUtilImpactPriority = 100;
db2Runstats (versionNumber, &runStatData, &sqlca);
DB2_API_CHECK("table -- runstats");
cout << "\n Viewing of runstat data on declared temporary tables or" << endl;
cout << " indexes on declared temporary tables is not supported" << endl;
return sqlca.sqlcode;
} // TbTemp::UpdateStatistics
// Use the Describe Command to describe the temporary table created.
// DESCRIBE TABLE command cannot be used with temp table.However,
// DESCRIBE statement can be used with SELECT statement to get
// table information. */
int TbTemp::DescribeTemporaryTable(void)
{
int i;
struct sqlca sqlca;
struct sqlda *pSqlda = (struct sqlda *)new char[SQLDASIZE(6)];
cout << "\n-----------------------------------------------------------";
cout << "\n Use the DESCRIBE COMMAND to describe temporary table" << endl;
strcpy(strStmt, "SELECT * FROM session.temptb1");
cout << "\n Perform:" << endl;
cout << "\n DESCRIBE SELECT * FROM session.temptb1" << endl;
pSqlda->sqln = 6;
// prepare the select statement
EXEC SQL PREPARE stmt FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
// Information regarding the SELECT statement is fetched into
// the sqlda structure variable
EXEC SQL DESCRIBE stmt INTO :*pSqlda;
EMB_SQL_CHECK("statement -- describe");
// Display the contents of the sqlda structure
cout << "\nSQLDA Information" << endl;
cout << "\n sqldaid: SQLDA sqldabc: "<< pSqlda->sqldabc
<< " sqln: " << pSqlda->sqln << " sqld: " << pSqlda->sqld <<endl;
cout << "\n Column Information\n\n";
cout <<" sqltype sqllen sqlname.data sqlname.length\n";
cout <<" --------------- ------ ------------------- --------------\n";
for(i = 0; i < pSqlda->sqld ; i++)
{
cout.setf(ios::right, ios::adjustfield);
cout << endl << " " << setw(4) << pSqlda->sqlvar[i].sqltype;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(10) << NumToAscii(pSqlda->sqlvar[i].sqltype);
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(6) << pSqlda->sqlvar[i].sqllen << " ";
cout.setf(ios::left, ios::adjustfield);
cout << setw(19) << pSqlda->sqlvar[i].sqlname.data ;
cout.setf(ios::right, ios::adjustfield);
cout << setw(14) << pSqlda->sqlvar[i].sqlname.length ;
}
cout << endl;
delete [] pSqlda;
return sqlca.sqlcode;
} // TbTemp::DescribeTemporaryTable
// Populate the temporary table with the department table's contents.
int TbTemp::PopulateTempTable(void)
{
// Populating the temporary table is done the same way as a normal table
// except the qualifier "session" is required whenever the table name
// is referenced.
cout << "\nUSE THE SQL STATEMENT:" << endl;
cout << " INSERT" << endl;
cout << "TO POPULATE THE DECLARED TEMPORARY TABLE WITH DATA FROM" << endl;
cout << "THE DEPARTMENT TABLE" << endl;
cout << "\n Perform:" << endl;
cout << " EXEC SQL INSERT INTO session.temptb1" << endl;
cout << " (SELECT deptno, deptname, mgrno, admrdept, location\n";
cout << " FROM department)" << endl;
EXEC SQL INSERT INTO session.temptb1
(SELECT deptno, deptname, mgrno, admrdept, location
FROM department);
EMB_SQL_CHECK("insert");
return sqlca.sqlcode;
} // TbTemp::PopulateTempTable
// Use cursors to access each row of the declared temporary table and then
// print each row. This function assumes that the declared temporary table
// exists. This access is the same as accessing a normal table except the
// qualifier, "session", is required in the table name.
int TbTemp::ShowTableContents(void)
{
cout <<"\n SELECT * FROM session.temptb1" << endl;
cout << " DEPT# DEPTNAME MGRNO ADMRDEPT LOCATION"
<< endl;
cout << " ----- ---------------------------- ------ -------- --------"
<< endl;
// Declare cursor
EXEC SQL DECLARE c1 CURSOR FOR SELECT * FROM session.temptb1;
// Open cursor
EXEC SQL OPEN c1;
EMB_SQL_CHECK("cursor -- open");
// Fetch cursor
EXEC SQL FETCH c1 INTO :deptno,
:deptname,
:mgrno :mgrnoInd, /* null value indicator */
:admrdept,
:location :locationInd; /* null value indicator */
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
if (mgrnoInd < 0)
{
// the manager number was a null value so print a hyphen instead
sprintf(mgrno, "-");
}
if (locationInd < 0)
{
// the location was a null value so print a hyphen instead
sprintf(location, "-");
}
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(5) << deptno;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(28) << deptname;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(6) << mgrno;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(8) << admrdept;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(8) << location;
cout.setf(ios::right, ios::adjustfield);
cout << endl;
EXEC SQL FETCH c1 INTO :deptno,
:deptname,
:mgrno :mgrnoInd, /* null value indicator */
:admrdept,
:location :locationInd; /* null value indicator */
EMB_SQL_CHECK("cursor -- fetch");
}
// Close cursor
EXEC SQL CLOSE c1;
EMB_SQL_CHECK("cursor -- close");
return 0;
} // TbTemp::ShowTableContents
// Drop the user temporary table space. This function assumes that the
// table space can be dropped. If the declared temporary table still exists
// in the table space, then the table space cannot be dropped.
int TbTemp::DropTablespace(void)
{
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENT:" << endl;
cout << " DROP TABLESPACE " << endl;
cout << "TO REMOVE THE TABLE SPACE THAT THIS PROGRAM CREATED" << endl;
cout << "\n Perform:" << endl;
cout << " EXEC SQL DROP TABLESPACE usertemp1" << endl;
EXEC SQL DROP TABLESPACE usertemp1;
EMB_SQL_CHECK("drop tablespace");
return sqlca.sqlcode;
} // TbTemp::DropTablespace
// DESCRIBE command returns the statement information in SQLDA structure.
// SQLTYPE field is returned as an integer and has to be mapped to the
// sql type name */
char * TbTemp::NumToAscii(int sqlTypeNum)
{
char * sqlTypeName = (char*)malloc(12 * sizeof(char));
switch(sqlTypeNum)
{
case 384 :
case 385 : strcpy(sqlTypeName, "DATE");
break;
case 388 :
case 389 : strcpy(sqlTypeName, "TIME");
break;
case 392 :
case 393 : strcpy(sqlTypeName, "TIMESTAMP");
break;
case 448 :
case 449 : strcpy(sqlTypeName, "VARCHAR");
break;
case 452 :
case 453 : strcpy(sqlTypeName, "CHARACTER");
break;
case 480 :
case 481 : strcpy(sqlTypeName, "FLOAT");
break;
case 484 :
case 485 : strcpy(sqlTypeName, "DECIMAL");
break;
case 496 :
case 497 : strcpy(sqlTypeName, "INTEGER");
break;
case 500 :
case 501 : strcpy(sqlTypeName, "SMALLINT");
break;
default : strcpy(sqlTypeName, "UNKNOWN TYPE");
}
return sqlTypeName;
} // TbTemp::NumToAscii
int main(int argc, char *argv[])
{
int rc = 0;
char dbAlias[SQL_ALIAS_SZ + 1];
char user[USERID_SZ + 1];
char pswd[PSWD_SZ + 1];
CmdLineArgs check;
DbEmb db;
TbTemp temp;
// Check the command line arguments
rc = check.CmdLineArgsCheck1(argc, argv, db);
if (rc != 0)
{
return rc;
}
cout << "\nTHIS SAMPLE SHOWS HOW TO USE DECLARED TEMPORARY TABLES." <<endl;
// Connect to database
cout << "\n-----------------------------------------------------------";
rc = db.Connect();
if (rc != 0)
{
return rc;
}
// Make sure a user temporary table space exists before creating the
// table
temp.CreateTablespace();
// Show how to make a declared temporary table
temp.DeclareTempTable();
// Show that the temporary table exists in ShowAfterCommit() even though
// it was declared in DeclareTempTable(). The temporary table is
// accessible to the whole session as the connection still exists at this
// point. Show that the temporary table exists after a commit.
temp.ShowAfterCommit();
// Declare the temporary table again. The old one will be dropped and a
// new one will be made.
temp.RecreateTempTableWithLogging();
// Create an index for the global temporary table
temp.CreateIndex();
// Update temporary table statistics using db2RunStats
temp.UpdateStatistics();
// Use the Describe Command to describe the temp table
temp.DescribeTemporaryTable();
// Disconnect from the database. This implicitly drops the temporary table.
// Alternatively, an explicit drop statement could have been used.
cout << "\n-----------------------------------------------------------";
rc = db.Disconnect();
if (rc != 0)
{
return rc;
}
cout << "THE DECLARED TEMPORARY TABLE IS IMPLICITLY DROPPED." << endl;
// Connect to database
cout << "\n-----------------------------------------------------------";
rc = db.Connect();
if (rc != 0)
{
return rc;
}
// Clean up - remove the table space that was created earlier.
// Note: The table space can only be dropped after the temporary table is
// dropped.
temp.DropTablespace();
// Disconnect from the database
cout << "\n-----------------------------------------------------------";
rc = db.Disconnect();
if (rc != 0)
{
return rc;
}
return rc;
} // main