Consultor Eletrônico

Solution ID: P2940

GOAL:

How to get the query plan for an SQL statement from an ESQL/C-92 client session

FACT(s) (Environment):

Progress 9.1x

FIX:

Sometimes a SQL-92 SELECT statement that uses a WHERE clause with the key fields referenced, seems to be pulling all the records in and not using the proper indexes. In that case, it is helpful to find out exactly what index was used in the query in order to compare it with what index should have been used.

This Solution contains sample code demonstrating how to get the SQL query plan for the previous SQL statement that an ESQL/C-92 client has performed.

The code is ready to be saved as a .pc file, pre-processed with the ESQLC preprocessor, compiled and run against the SPORTS2000 database.


/**********************************************************
* Sample ESQL-92 code to connect to DEFAULT database, perform a static
* select statement and get the SQL query plan from the previous statement
* The database, username and password have to be specified with the
* env variables: DB_NAME, DH_USER, DH_PASSWD
***********************************************************/

static void query1() ;
struct sqlca sqlca;
dh_i32_t SQLCODE;

main ()
{
EXEC SQL WHENEVER SQLERROR GOTO mainerr ;
EXEC SQL CONNECT TO DEFAULT ;
query1 () ;
EXEC SQL DISCONNECT DEFAULT ;

exit (0) ;

mainerr:
if (sqlca.sqlcode)
{
printf ("SQL Error (%ld) %s\n", sqlca.sqlcode, sqlca.sqlerrm) ;
exit (1) ;
}
} /* end main() */

static void query1 ()
{
char errmesg[80] ;
int i,j ;
EXEC SQL BEGIN DECLARE SECTION ;
char sel_stmt_v[1000] ; /* INPUT SELECT stmt */
char char_p_v[100] ; /* OUTPUT CHAR column */
EXEC SQL END DECLARE SECTION ;

strcpy(sel_stmt_v, "SELECT Name FROM PUB.Customer WHERE CustNum < 10") ;
printf("%s\n", sel_stmt_v);

EXEC SQL WHENEVER SQLERROR GOTO selerr ;
EXEC SQL PREPARE stmtid from :sel_stmt_v ;
EXEC SQL DECLARE dyncur CURSOR FOR stmtid ;
EXEC SQL OPEN dyncur ;
EXEC SQL WHENEVER NOT FOUND GOTO sel1done ;

j = 100;
for (i = 0; i < j; i++)
{
EXEC SQL FETCH dyncur INTO
:char_p_v ;
if (i == 0)
{
printf ("\n 1st col \n");
printf (" --------\n");
}
printf (" %s \n", char_p_v) ;
}

selerr:
if (sqlca.sqlcode < 0)
{
strncpy (errmesg, sqlca.sqlerrm, sqlca.sqlerrml);
errmesg[sqlca.sqlerrml] = ' ' ;

printf ("SQL Error : %s\n", errmesg);
}
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK ;
exit (1);

sel1done:
EXEC SQL CLOSE dyncur ;

query2:
{
EXEC SQL BEGIN DECLARE SECTION ;
char sel_stmt2_v[1000] ; /* INPUT SELECT stmt for query plan */
char char_p2_v[3000] ; /* OUTPUT CHAR column for query plan */
EXEC SQL END DECLARE SECTION ;

strcpy(sel_stmt2_v, "SELECT substring(\"_Description\",1,80) ");
strcat(sel_stmt2_v, " FROM PUB.\"_Sql_Qplan\" " );
strcat(sel_stmt2_v, " WHERE \"_Pnumber\" = (SELECT max(\"_Pnumber\") ");
strcat(sel_stmt2_v, " FROM PUB.\"_Sql_Qplan\" ") ;
strcat(sel_stmt2_v, " WHERE \"_Ptype\" > 0 ) ") ;

printf (" Getting SQL Query plan:\n") ;
printf("%s\n", sel_stmt2_v);

EXEC SQL WHENEVER SQLERROR GOTO selerr ;
EXEC SQL PREPARE stmtid2 from :sel_stmt2_v ;
EXEC SQL DECLARE dyncur2 CURSOR FOR stmtid2 ;
EXEC SQL OPEN dyncur2 ;
EXEC SQL WHENEVER NOT FOUND GOTO sel2done ;

while ( 1 )
{
EXEC SQL FETCH dyncur2 INTO
:char_p2_v ;
printf (" %s \n", char_p2_v) ;
}

sel2done:
EXEC SQL CLOSE dyncur2 ;
}
return ;
} /* end query1() */