Repository: incubator-trafodion Updated Branches: refs/heads/master 25da9af62 -> 7092997e2
[TRAFODION-2298] Fix issue with incremental stats and LOB columns Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/98561ca5 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/98561ca5 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/98561ca5 Branch: refs/heads/master Commit: 98561ca51012b154422da024e45f115e2e957da0 Parents: 196e6f4 Author: Dave Birdsall <dbirds...@apache.org> Authored: Wed Oct 19 22:51:04 2016 +0000 Committer: Dave Birdsall <dbirds...@apache.org> Committed: Wed Oct 19 22:51:04 2016 +0000 ---------------------------------------------------------------------- core/sql/bin/SqlciErrors.txt | 2 +- core/sql/ustat/hs_const.h | 1 + core/sql/ustat/hs_update.cpp | 19 ++++++++++++++++++- .../asciidoc/_chapters/update_stats_msgs.adoc | 15 +++++++++++++++ 4 files changed, 35 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/98561ca5/core/sql/bin/SqlciErrors.txt ---------------------------------------------------------------------- diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt index 5c090c2..854adf8 100644 --- a/core/sql/bin/SqlciErrors.txt +++ b/core/sql/bin/SqlciErrors.txt @@ -1868,7 +1868,7 @@ drop the default context 9217 ZZZZZ 99999 BEGINNER INFRM DBADMIN The statement will have no effect because no histograms are currently maintained for the table. 9218 ZZZZZ 99999 BEGINNER INFRM DBADMIN The statement will have no effect because no histograms need to be updated. 9219 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS: An operation failed, possibly due to an invalid WHERE clause. -9220 ZZZZZ 99999 BEGINNER INFRM DBADMIN --- unused --- +9220 ZZZZZ 99999 BEGINNER INFRM DBADMIN The table is empty, so no persistent sample table was created. 9221 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Incremental UPDATE STATISTICS cannot be performed due to the absence of the IUS persistent sample table for $0~string0. Use a regular UPDATE STATISTICS command with the sample clause and PERSISTENT first to create such a persistent sample table. 9222 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: the rowcount in an interval for column $0~String0 changed more than the percentage specified by CQD USTAT_IUS_INTERVAL_ROWCOUNT_CHANGE_THRESHOLD. A regular UPDATE STATISTICS is performed instead. 9223 ZZZZZ 99999 BEGINNER MINOR LOGONLY Incremental UPDATE STATISTICS: the total rowcount for column $0~String0 changed more than the percentage specified by CQD USTAT_IUS_TOTAL_ROWCOUNT_CHANGE_THRESHOLD. A regular UPDATE STATISTICS is performed instead. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/98561ca5/core/sql/ustat/hs_const.h ---------------------------------------------------------------------- diff --git a/core/sql/ustat/hs_const.h b/core/sql/ustat/hs_const.h index 3646a38..b4f8b12 100644 --- a/core/sql/ustat/hs_const.h +++ b/core/sql/ustat/hs_const.h @@ -145,6 +145,7 @@ enum USTAT_ERROR_CODES {UERR_SYNTAX_ERROR = 15001, UERR_WARNING_NO_EXISTING_HISTOGRAMS = 9217, UERR_WARNING_NO_OBSOLETE_HISTOGRAMS = 9218, UERR_IUS_BAD_WHERE_CLAUSE = 9219, + UERR_WARNING_NO_SAMPLE_TABLE_CREATED = 9220, UERR_IUS_NO_PERSISTENT_SAMPLE = 9221, UERR_WARNING_IUS_TOO_MUCH_RC_CHANGE_INTERVAL = 9222, UERR_WARNING_IUS_TOO_MUCH_RC_CHANGE_TOTAL = 9223, http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/98561ca5/core/sql/ustat/hs_update.cpp ---------------------------------------------------------------------- diff --git a/core/sql/ustat/hs_update.cpp b/core/sql/ustat/hs_update.cpp index 080af79..4a4f1a4 100644 --- a/core/sql/ustat/hs_update.cpp +++ b/core/sql/ustat/hs_update.cpp @@ -313,6 +313,13 @@ Lng32 UpdateStats(char *input, NABoolean requestedByCompiler) retcode = HSFuncExecQuery("CONTROL QUERY DEFAULT TRAF_ALLOW_RESERVED_COLNAMES 'ON'"); HSExitIfError(retcode); + // Set the following so we will see LOB columns as LOB columns and not as + // varchars + retcode = HSFuncExecQuery("CONTROL QUERY DEFAULT TRAF_BLOB_AS_VARCHAR 'OFF'"); + HSExitIfError(retcode); + retcode = HSFuncExecQuery("CONTROL QUERY DEFAULT TRAF_CLOB_AS_VARCHAR 'OFF'"); + HSExitIfError(retcode); + LM->StopTimer(); @@ -452,7 +459,12 @@ Lng32 UpdateStats(char *input, NABoolean requestedByCompiler) retcode = hs_globals_obj.CollectStatistics(); HSExitIfError(retcode); } - + else if (hs_globals_obj.optFlags & IUS_PERSIST) + { + // The user asked for a persistent sample, but the table is empty + // so we didn't create one. Tell the user that. + HSFuncMergeDiags(UERR_WARNING_NO_SAMPLE_TABLE_CREATED); + } // do not care about warning messages now retcode = HSFuncExecQuery("CONTROL QUERY DEFAULT HIST_MISSING_STATS_WARNING_LEVEL RESET"); @@ -537,6 +549,11 @@ Lng32 UpdateStats(char *input, NABoolean requestedByCompiler) } } #endif + + // Reset CQDs set above; ignore errors + HSFuncExecQuery("CONTROL QUERY DEFAULT TRAF_BLOB_AS_VARCHAR RESET"); + HSFuncExecQuery("CONTROL QUERY DEFAULT TRAF_CLOB_AS_VARCHAR RESET"); + LM->StopTimer(); return retcode; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/98561ca5/docs/messages_guide/src/asciidoc/_chapters/update_stats_msgs.adoc ---------------------------------------------------------------------- diff --git a/docs/messages_guide/src/asciidoc/_chapters/update_stats_msgs.adoc b/docs/messages_guide/src/asciidoc/_chapters/update_stats_msgs.adoc index 9c7d9d9..c660148 100644 --- a/docs/messages_guide/src/asciidoc/_chapters/update_stats_msgs.adoc +++ b/docs/messages_guide/src/asciidoc/_chapters/update_stats_msgs.adoc @@ -312,6 +312,21 @@ update failed. This message is accompanied by another message giving more detail *Recovery:* If the WHERE clause is in error, correct and resubmit. If this does not correct the problem, contact {project-support}. +[[SQL-9220]] +== SQL 9220 + +``` +The table is empty, so no persistent sample table was created. +``` + +*Cause:* UPDATE STATISTICS SAMPLE RANDOM PERSISTENT was specified but the table was empty. Histograms +were created, however no persistent sample table was created. + +*Effect:* The operation completes. + +*Recovery:* If you wish to create a persistent sample table, either increase the sampling rate +or populate the table with more data, and resubmit. + <<< [[SQL-9221]] == SQL 9221
