Method designat If no method with the specified signature exists for the type in the named or implied schema, an error (SQLSTATE 42883)is raised FOR type-name Names the type with which the specified method is to be associated The name must identify a type already described in the catalog (SQLSTATE 42704). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names SPECIFIC METHOD specific-name Identifies a particular method, using the name that is specified or defaulted to at method creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific method instance in the named or implied schema; otherwise, an error (SQLSTATE 42704)is raised Procedure designator A procedure designator uniquely identifies a single stored procedure Procedure designators typically appear in DDL statements for procedures (such as DRoP or ALTER) Syntax SPECIFIC PROCEDURE--specific-name PROCEDURE procedure-name Identifies a particular procedure, and is valid only if there is exactly one procedure instance with the name procedure-name in the schema. The dentified procedure can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER Precompile/bind option implicitly specifies the qualifier for unqualified object names. If no procedure by this name exists in the XIV SQL Reference, Volume 2
If no method with the specified signature exists for the type in the named or implied schema, an error (SQLSTATE 42883) is raised. FOR type-name Names the type with which the specified method is to be associated. The name must identify a type already described in the catalog (SQLSTATE 42704). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. SPECIFIC METHOD specific-name Identifies a particular method, using the name that is specified or defaulted to at method creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific method instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised. Procedure designator A procedure designator uniquely identifies a single stored procedure. Procedure designators typically appear in DDL statements for procedures (such as DROP or ALTER). Syntax: procedure-designator: PROCEDURE procedure-name ( ) , ( data-type ) SPECIFIC PROCEDURE specific-name Description: PROCEDURE procedure-name Identifies a particular procedure, and is valid only if there is exactly one procedure instance with the name procedure-name in the schema. The identified procedure can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. If no procedure by this name exists in the Method designator xiv SQL Reference, Volume 2
Procedure designator named or implied schema, an error(SQLSTATE 42704)is raised. If there is more than one instance of the procedure in the named or implied schema, an error(SQLSTATE 42725)is raised PROCEDURE procedure-name(data-type) Provides the procedure signature, which uniquely identifies the procedure. The procedure resolution algorithm is not used proceaure-nanme Specifies the name of the procedure In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names Values must match the data types that were specified (in the rresponding position) on the CREAtE PROCEDURE statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific procedure instance If a data type is unqualified, the type name is resolved by searching the schemas on the SQL Path. This also applies to data type names specified for a REFERENCE typ It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match FLOATO cannot be used(SQLSTATE 42601), because the parameter value indicates different data types (real or DOUBLE) If length, precision, or scale is coded, the value must exactly match that specified in the CREATE PROCEDURE statement A type of FLOAT(n) does not need to match the defined value for n, because n<25 means REAL, and 24< n< 54 means DOuBle Matching occurs on the basis of whether the type is REAL or DOUBLE If no procedure with the specified signature exists in the named or implied schema, an error(SQLSTATE 42883)is raised SPECIFIC PROCEDURE specific-name Identifies a particular procedure, using the name that is specified or defaulted to at procedure creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified About this book x
named or implied schema, an error (SQLSTATE 42704) is raised. If there is more than one instance of the procedure in the named or implied schema, an error (SQLSTATE 42725) is raised. PROCEDURE procedure-name (data-type,...) Provides the procedure signature, which uniquely identifies the procedure. The procedure resolution algorithm is not used. procedure-name Specifies the name of the procedure. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. (data-type,...) Values must match the data types that were specified (in the corresponding position) on the CREATE PROCEDURE statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific procedure instance. If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type. It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). If length, precision, or scale is coded, the value must exactly match that specified in the CREATE PROCEDURE statement. A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE. If no procedure with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is raised. SPECIFIC PROCEDURE specific-name Identifies a particular procedure, using the name that is specified or defaulted to at procedure creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified Procedure designator About this book xv
Procedure desig object names. The specific-name must identify a specific procedure instance in the named or implied schema; otherwise, an error(SQLSTATE 42704)is Conventions used in this manual This section specifies some conventions which are used consistently throughout this manual Error conditions An error condition is indicated within the text of the manual by listing the SQLSTATE associated with the error in parentheses. For example A duplicate signature raises an SQL error (SQLSTATE 42723) Highlighting conventions The following conventions are used in this book Bold Indicates commands, keywords, and other items whose names are predefined by the system. Indicates one of the following Names or values(variables) that must be supplied by the user. General emphasis The introduction of a new term A reference to another source of information Monospace Indicates one of the following Information that you are instructed to type at a command prompt or Examples of specific dat Examples of text similar to what may be displayed by the system Examples of system messages Related documentation The following publications may prove useful in preparing applications Contains information required to design, implement, and maintain a database to be accessed either locally or in a client/server environment Application Development Guide Discusses the application development process and how to code, compile, and execute application programs that use embedded SQL and APis to access the database XVI SQL Reference, Volume 2
object names. The specific-name must identify a specific procedure instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised. Conventions used in this manual This section specifies some conventions which are used consistently throughout this manual. Error conditions An error condition is indicated within the text of the manual by listing the SQLSTATE associated with the error in parentheses. For example: A duplicate signature raises an SQL error (SQLSTATE 42723). Highlighting conventions The following conventions are used in this book. Bold Indicates commands, keywords, and other items whose names are predefined by the system. Italics Indicates one of the following: v Names or values (variables) that must be supplied by the user. v General emphasis. v The introduction of a new term. v A reference to another source of information. Monospace Indicates one of the following: v Files and directories. v Information that you are instructed to type at a command prompt or in a window. v Examples of specific data values. v Examples of text similar to what may be displayed by the system. v Examples of system messages. Related documentation The following publications may prove useful in preparing applications: v Administration Guide – Contains information required to design, implement, and maintain a database to be accessed either locally or in a client/server environment. v Application Development Guide – Discusses the application development process and how to code, compile, and execute application programs that use embedded SQL and APIs to access the database. Procedure designator xvi SQL Reference, Volume 2
Related documentation DB2 Universal Database for iSeries SQL Referenc This book defines Structured Query Language(SQL) as supported by DB2 Query Manager and SQL Development Kit on iSeries(AS/400).It contains reference information for the tasks of system administration, database administration, application programming, and operation. This manual includes syntax, usage notes, keywords, and examples for eac of the SQL statements used on iSeries(AS/400)systems running DB2 DB2 Universal Database for z/OS and OS/390 SQL Reference This book defines Structured Query Language(SQL) used in DB2 for /OS(OS/390 ). It provides query forms, SQL statements, SQL procedure statements, DB2 limits, SQLCA, SQLDA, catalog tables, and SQL reserved words for z/OS(OS/390) systems running DB2. DB2 Spatial Extender uIser's Guide and reference This book discusses how to write applications to create and use a geographic information system(GIS). Creating and using a GiS involves supplying a database with resources and then querying the data to obtain information such as locations, distances, and distributions within areas. IBM SQL Re erence This book contains all the common elements of SQL that span IBM,s database products. It provides limits and rules that assist in preparing portable programs using IBM databases. This manual provides a list of SQL extensions and incompatibilities among the following standard products: SQL92E, XPG4-SQL, IBM-SQL and the IBM relational datal American National Standard X3. 135-1992, Database Language SQL Contains the ANSI standard definition of SQL. ISO/EC 9075: 1992, Database Language SQL Contains the 1992 ISO standard definition of SQL ISO/EC 9075-2: 1999, Database Language SQL -Part 2: Foundation (SQL/Foundation) Contains a large portion of the 1999 ISO standard definition of SQL. ISO/IEC 9075-4: 1999, Database Language SQL -Part 4: Persistent Stored Modules(SQL/PSM Contains the 1999 ISO standard definition for SQL procedure control statements ISO/EC 9075-5: 1999, Database Language SQL -Part 4: Host Language Bindings (SQL/Bindings) Contains the 1999 ISO standard definition for host language bindings About this book xVI
v DB2 Universal Database for iSeries SQL Reference – This book defines Structured Query Language (SQL) as supported by DB2 Query Manager and SQL Development Kit on iSeries (AS/400). It contains reference information for the tasks of system administration, database administration, application programming, and operation. This manual includes syntax, usage notes, keywords, and examples for each of the SQL statements used on iSeries (AS/400) systems running DB2. v DB2 Universal Database for z/OS and OS/390 SQL Reference – This book defines Structured Query Language (SQL) used in DB2 for z/OS (OS/390). It provides query forms, SQL statements, SQL procedure statements, DB2 limits, SQLCA, SQLDA, catalog tables, and SQL reserved words for z/OS (OS/390) systems running DB2. v DB2 Spatial Extender User’s Guide and Reference – This book discusses how to write applications to create and use a geographic information system (GIS). Creating and using a GIS involves supplying a database with resources and then querying the data to obtain information such as locations, distances, and distributions within areas. v IBM SQL Reference – This book contains all the common elements of SQL that span IBM’s database products. It provides limits and rules that assist in preparing portable programs using IBM databases. This manual provides a list of SQL extensions and incompatibilities among the following standards and products: SQL92E, XPG4-SQL, IBM-SQL and the IBM relational database products. v American National Standard X3.135-1992, Database Language SQL – Contains the ANSI standard definition of SQL. v ISO/IEC 9075:1992, Database Language SQL – Contains the 1992 ISO standard definition of SQL. v ISO/IEC 9075-2:1999, Database Language SQL -- Part 2: Foundation (SQL/Foundation) – Contains a large portion of the 1999 ISO standard definition of SQL. v ISO/IEC 9075-4:1999, Database Language SQL -- Part 4: Persistent Stored Modules (SQL/PSM) – Contains the 1999 ISO standard definition for SQL procedure control statements. v ISO/IEC 9075-5:1999, Database Language SQL -- Part 4: Host Language Bindings (SQL/Bindings) – Contains the 1999 ISO standard definition for host language bindings and dynamic SQL. Related documentation About this book xvii
Related documentation XVIll SQL Reference
Related documentation xviii SQL Reference, Volume 2