vb059自动点歌系统sql外文译文及原文

1、SQLSQLDB2本术语和概念。过程化 SQL 构造（例如标量变量、IF 语句和 循环）在 DB2UniversalDatabase(UDB)版中引入 DB2 的。以前的 DB2过程，以及CJavaSQLSQLDB2本术语和概念。过程化 SQL 构造（例如标量变量、IF 语句和 循环）在 DB2UniversalDatabase(UDB)版中引入 DB2 的。以前的 DB2过程，以及CJava识列SQL，DB2SQL言，节主要是由DB2优化器为该查询选择的存取方SQLDLLDB2DB2 ）减少频繁调用的过程（例如 OLTP）程语言（SQLPL）。后来（DB2UDBV7.2），SQLSQLPL。

2、“内联”一词突出显示了它与完整语言的重要区别。SQLPLSQLSQLPLSQLPL两类条件构造：过程型（IFCASE）和函数型（CASECASECASEIFSQLDMLSQLPL。“内联”一词突出显示了它与完整语言的重要区别。SQLPLSQLSQLPLSQLPL两类条件构造：过程型（IFCASE）和函数型（CASECASECASEIFSQLDMLDML首先，请确保不使用高于您所需级别DB2（Repeatable SQLINSERTDELETEWHERECURRENTOFINSERTDELETEWHERECURRENTOFUR），DB2SELECTFORREADONLY为不可删除，或者通过在 S

3、ELECT 语句中使用 选项），DB2FORREADONLY，DB2BLOCKINGNOBLOCKINGALL 绑定选项，那么对于含混游标（既不是定义成 FORREADONLYFORUPDATE），DB2FORREADONLYBLOCKING ALL 绑定选项。要设置 还可以使用 SQLSQLSQLSQLSQL，SQL会改变数据库状态的语句（INSERTUPDATEDELETESQLPLSQL（CALL游标和条件处理）SQLSQLSQLSQLV7，DB2会改变数据库状态的语句（INSERTUPDATEDELETESQLPLSQL（CALL游标和条件处理）SQLSQLSQLSQLV7，DB2的S

4、QLCONNECTRESET高速缓存发现它时，DB2索引并对它们运s（显然后者是填充了表后进行的）SQL必须在嵌套的复合语句中定义结果集的理由是，DECLAREGLOBALTABLE语句（例如 DECLARECURSORGustavo ArocenaDB2SQL1998IBM索引并对它们运s（显然后者是填充了表后进行的）SQL必须在嵌套的复合语句中定义结果集的理由是，DECLAREGLOBALTABLE语句（例如 DECLARECURSORGustavo ArocenaDB2SQL1998IBMSQL资开, IBM .2005 年 2SQLSQLServerSybaseSQL其次是缓存过程中的

5、代码是预先编译的。而应用程序中的代为了解决第一个问题，DB2SQLPLDB2SQL真是令人印象深刻！仅仅通过使用内联SQLPL就使SQLMERGESQLPL在定义上是私有的。与之相比，DB2真是令人印象深刻！仅仅通过使用内联SQLPL就使SQLMERGESQLPL在定义上是私有的。与之相比，DB2UDB for zOS表（CreatedGlobalTemporaryTable）DGTTDECLARE 和 DROP 之间的 insert 语句每次都要重新进行编译，因为 continueAPPENDONLYDELETEDB2为DB2选择：清空（truncate）DELETE第操作之后，检查错误。在

6、 DB2编写 C-或 已经学会在其代码的每条 EXEC SQLSQLCA或特定错误条件。Java 和 C+SQL/PSMSQL当从 SybaseSQL 服务器移植 TSQL 过程时，或当仅仅对 SQLCDB2选择：清空（truncate）DELETE第操作之后，检查错误。在 DB2编写 C-或 已经学会在其代码的每条 EXEC SQLSQLCA或特定错误条件。Java 和 C+SQL/PSMSQL当从 SybaseSQL 服务器移植 TSQL 过程时，或当仅仅对 SQLCONOTFOUND（如果想这样做的话部变量中保存 SQLCODE 和 There are many sourof advic

7、eavailable on how to tune database systemsand applications. Articles like DB2 Tuning Tips for OLTP Applications, published on the IBM DB2 , provide advice on topics from tablespace and index design to memory allocation for ools, There are many sourof adviceavailable on how to tune database systemsan

8、d applications. Articles like DB2 Tuning Tips for OLTP Applications, published on the IBM DB2 , provide advice on topics from tablespace and index design to memory allocation for ools, ion and data parallelism yzing query plans. These topicsare the of performanceHowever, specific advice on how anize

9、 the the stored t is the kind of themselvesn eye on performance isI his article. Even though the article focuses on SQL procedures, most the information provided here is applicable to SQLlogic embedded in applications in stored procedureswritteninotherBefore o the details, review some basic terminol

10、ogy concepts related to procedural SQL in DB2. Procedural SQL constructs (such scalar variables, IF ements and WHILE loops) roduced in DB2 with release of DB2 Universal DatabaseTM (UDB) 7. Prior releases of supportedCandJavaTMaslanguagesforstoredprocedures. roducedstoredWhen an SQL procedure is crea

11、ted, DB2 separates the SQL queries in procedure body from the procedural logic. ize performance, the queries are ically o sections in a package. (For a ically query, a section consists mainly of the s plan selected by the DB2 optimizer t query. A package is a collection of sections. For more informa

12、tion on and sections, please refer to the DB2 SQL Reference,Volume1.) On the other the procedural logicis oaDLL(dynamicallylinked During the execution of a procedure, every time control flows from procedural logic to an SQL ement, there is a context switch n the andtheDB2engine. (In DB2 V8,SQLproced

13、uresrun in unfenced mode, same addressing space as the DB2 engine. Therefore the context switch we refer to here is not a full context switch at the operating system level, but rather a change layer within DB2.) Reducing the number of context switches in t invoked very often (such as procedures in a

14、n OLTP application) same addressing space as the DB2 engine. Therefore the context switch we refer to here is not a full context switch at the operating system level, but rather a change layer within DB2.) Reducing the number of context switches in t invoked very often (such as procedures in an OLTP

15、 application) t s numbers of rows (for le, t perform data cleansing) can have noticeable impact on their performance. Several of the tipshisarticleaimprecisely at reducing these context switches.The SQL procedural language (commonly referred to as SQL PL) was allowed only in SQL procedures (DB2 Univ

16、ersal Database V7 GA). Later on UDB V7.2), a subset of the language started to be supported in SQL functions trigger bodies. bset of SQL PL is known as inline SQL PL. The word highlights an important difference with the full language. Whereas an SQL procedure is implemented by ically compiling its i

17、ndividual SQL osections in a package, an inline SQL PL function is implemented, as the suggests, by inlining the body of the o the t uses it. Well inlineSQLPLlateron,along withlesof itsFrommultipleSQLementsto asingleSQLLike other programming languages, the conditional constructs: procedural (IF and

18、CASE language provides two types ements) and functional (CASE type can be used to express s). In most where ion, using one or the other is a matter of taste. However, logic written CASE s is not only more compact, but also more n writtenusing CASEorIFExploit the -a-time s of SQL,Procedural ch as ass

19、ignment and cursors allow us to express t would not to at our sing just SQL DML ements. But when we have procedural al, there is a t we could turn to them even when the at hand can, in fact, be expressed using just SQL DML ements. As mentioned r, the performance of a procedural ion can be orders mag

20、nitude nthe performance of anequivalent ion expressed When looking at improving the performance of existing procedural logic, any time spent in eliminating cursor loops will likely pay off.If the logic in your stored procedures does require cursors, here are a few When looking at improving the perfo

21、rmance of existing procedural logic, any time spent in eliminating cursor loops will likely pay off.If the logic in your stored procedures does require cursors, here are a few to nmind ize theirofall,makesure you dont use an isolation level n what you The isolation level determines the amount of t D

22、B2 s on the t the procedure reads or updates. The higher the isolation level, the more DB2 will perform, and therefore, the less concurrency n competing for the same . For instance, a procedure using Repeatable (RR) isolation level will cause share locks on any row it reads, whereas a using Cursor S

23、tability (CS) will only lock the current row of any updatable DB2_SQLROUTINE_PREPOPTSregistryThe default isolation level in DB2 is Cursor Stability. But of course, to the correctness of an application, it is sometimes sary to use Repeatable What is importanttoremember istorevertDB2_SQLROUTINE_PREPOP

24、TS backto a lower isolation level once procedures requiring Repeatable Read are created.The query above will execute with isolation level UR regardless the levelspecified in A related e to keep in mind when trying to improve cursor performance cursor updatability. A cursor is deletable if the rows i

25、t ranges over can be updated deleted usingtheWHERE CURRENTOFclause in UPDATE orDELETE When a cursor is deletable, DB2 has to obtain exclusive locks (as ed to locks) on rows, and cannot perform row blocking. An exclusive lock on a prevents other applications from even reading the row (they have to wa

26、it until the isreleased, unlesstheir isolation levelisUR),whereasrow blockingmanager overhead forcursors byretrievinga blockofrowsinasingle Row blocking can only happen for non-deletable t is why it important to let DB2 know howa cursor isgoing to be used. Cursors canRow blocking can only happen for

27、 non-deletable t is why it important to let DB2 know howa cursor isgoing to be used. Cursors can be declaredasnon-yspecifying theFORREADONLYhe s ement, or as deletable by using the FOR UPDATE clause in the SELECT s ement. Based on this information (and also on the BLOCKING option described below), D

28、B2 will decide whether to use row blocking for a given cursor or not.By default, DB2 will always use row blocking forcursors defined using the READ ONLY clause, unless the BLOCKING NO bind option has been specified. the other hand, DB2 will use row blocking for ambiguous cursors t are defined aseith

29、er FORREADONLYorFOR UPDATE) if theBLOCKING ALLoption is To make a long story short: sible, use the FOR READ ONLY clause your cursor definitions; if your procedures contain ambiguous cursors, use the BLOCKING ALL bind option. To set the value of the BLOCKING bind option, we also use the DB2_SQLROUTIN

30、E_PREPOPTS registry variable.In the absence of side-effects, use SQL functions.As we mentioned in the roduction, SQL procedures and SQL functions are implemented using different technologies. Queries in an SQL procedure are compiled individually, each of ing a section in a package. The compilation o

31、ccurs when the procedure created, and the queries are piled until the procedure is recreated or until ted package is On the other hand, queries in SQL functions are compiled together, as if functionbody wereasingle query.The compilation occursevery time ausesthe function istUnlikeppensinSQLprocedure

32、s, procedural ementsin SQLare not executed in a different n dataflow ements. Therefore, there is context switch every time control flows from a procedural to a dataflow ement viceAs a result of these , a given piece of procedural code will execute much faster when implemented as a n when implemented

33、 as procedure. But of course, there is a catch. Functions can only contain tdo not alter the e of the database (for le, INSERT, UPDATE or ements are not allowed). And also only procedure. But of course, there is a catch. Functions can only contain tdo not alter the e of the database (for le, INSERT,

34、 UPDATE or ements are not allowed). And also only a subset of the full SQL PL language is allowed in SQL functions (no CALL sements, no cursors, no condition handling).Despite these restrictions, most SQL procedures with no side-effects can be o SQL functions.So, as the title of this section indicat

35、es, consider using SQL functions instead of procedureswhenyourejustextractingdatafromthedatabasewithoutperforminganyUsetemporarytablesfortemporaryIn 7, roduced temporary tables. Operations on temporary n operations on regular tables. Lets look at some of are usually much To begin with, the creation

36、of a temporary table does not involve the insertion he catalogs, and use of the temporary table does not involve s to catalogseither; therefore, thereisno catalogBecause temporary tables are only sible to the t created there is no lockingheirIf the NOT LOGGED option is specified, operations on tempo

37、rary tables are logged (at the expense, of course, of Therefore,ifyour stored procedures sibility of rolling back argeamountsof temporary hat only meant to be used within a with the database, storing those data temporarytablescan resultin significantperformanceBefore any use of a temporary table in

38、an SQL procedure, the table mustbe hecompilation After the CONNECTd, the temporary table willno longer At runtime, the application will have to t the table exists before t uses it is executed. This last observation brings up an important havent mentioned yet: any t a temporary table will be dynamica

39、lly, even if the queryiswritten asicSQL. Like any otherdynamicafter the query is compiled, it will stay in compiled he package cache. next time the same query is executed, DB2 the pile it only if it is not found If you plan to create relatively after the query is compiled, it will stay in compiled h

40、e package cache. next time the same query is executed, DB2 the pile it only if it is not found If you plan to create relatively big temporary tables and run several queries them, consider defining indexes and running after the tablehasbeen s on them (the latter One last comment on using temporary ta

41、bles in SQL procedures: if you need return aresult set based on atemporary tishesameprocedure, resultset must bedefinedin anestedcompound The reason the result set must be defined in a nested compound ement tthe DECLARE GLOBAL TEMPORARY TABLE is an executable ement, executable sements can only be wr

42、itten after declaration sements such as DECLARE CURSOR. If we declared the table in the outer scope after the definition, the table would not be he compilation environment when DECLARE CURSOR ement is compiled, and therefore the compilation KeeptheDB2optimizerWhen a procedure is created, its individ

43、ual SQL queries are osections in a package. The DB2 optimizer chooses an execution plan for a based, among other things, on table istics (for le, table sizes or the frequency of data values in a column) and indexes available at the time the query compiled. When ffer significant changes, it may be a

44、good idea to let collect istics on these tables again. And when istics are updated, or when indexes are created it may also be a good idea to rebind the packages ted SQLand t use the tables, to let DB2 create t exploit the latest sTable istics can be updated using the d. To rebind REBIND_ROUTINE_PAC

45、KAGEbuilt-inprocedure(availableinDB2AbouttheGustavo Arocena is a technical manager in the DB2 SQL Compiler area. He joined the IBM Toronto Lab in 1998 and is currently responsible for SQL andAbouttheGustavo Arocena is a technical manager in the DB2 SQL Compiler area. He joined the IBM Toronto Lab in

46、 1998 and is currently responsible for SQL and SQL Parser development. Gustavo holds a Masters degree in Computer fromtheUniversityofhearea ofdatabase query Senior SoftwareDeveloper,IBM27 Jan ThetrivialSQLIf you have SQL Server or Sybase background, you know procedures very well. The paradigm is to

47、ve any he application naCALLement. Theperceivedbenefit ofthisparadigm is, there is encapsulation. For le, if a query is complex, it should not repeatedmultipletimes.Instead itshould be storedin one Second, thereis caching. The code ina storedprocedure is the application often is dynamic code.piled.

48、The code To address ie, DB2 supports the concept of inline SQL PL. allows simple logic or queries to be o SQL functions. When the functionisexecutedfrom thecaller,itsbodyismacro o theTo address the second e, DB2 uses its package cache. This cache not onlyrecently executed procedures.Thecachealso rem

49、emberspreviously ements, t after compilation of the SQL ement. invocationswillsimply keepexecutingthesameexecution Impressive! The monitored code ran three times faster just by using inline SQL PL. Even simple SQL Procedures, which do not return result sets, can be replaced with SQL Functions. For p

50、rocedures t return more n one argument, you can use a SQL table function, which returns a table of one row - one column per argument. For procedures with a single output, you can simply use a scalar function. t DB2 V8.2 also supports UPDATE, DELETE, INSERT, MERGE in SQL Table Functions. This means y

51、ou can even encapsulate modificationsusing inlineSQLDB2 supports Declared Global Temporary Table (DGTT). In SQL DECLARE t the object being defined willmodificationsusing inlineSQLDB2 supports Declared Global Temporary Table (DGTT). In SQL DECLARE t the object being defined will ve an he Therefore,it

52、 isbydefinition private.Bycontrast,CreatedGlobal TemporaryTablessupported by DB2 UDB for zOS are of course, private,their definitionishe catalogs. While their content The flexibility of having a declared object (no DBA cares what the app does long as user temporary table space is readily available)

53、comes downside: For ement to be compiled, the object must exist. Ifthe connection is lost or the table dropped, any ement referring to the DGTT needs to tableisre-piled every time The insert ement n the DECLARE and the DROP needs to piled every time because DB2 cannot know whether the DGTT will have

54、 same property next time around. In reality there will be any number of n the DECLARE and the DROP massaging the result set of this temp until finally hasserved itsTo avoid this compilation frenzy, it is much more appropriate to move declaration of the workload iso a separate procedure, which is exe

55、cuted once when Temporary tables are optimized for speed, so DB2 does not bother finding free space within a temp when inserting rows. Instead the table behaves as APPEND ONLY. While a regular DELETE would delete the rows, it would not actually cause DB2 to recycle the space. The application will keep on consuming more and more user temporary table space.Ironically, anot