DB2 commonly used SQL statement

  1, the implementation of portfolio statements 

  BEGIN ATOMIC 
  Expression of a semicolon spaces / 2 semicolon Enter expression spaces / Enter 
END

  2, should be limited access to (these should be withdrawn PUBLIC SELECT access to the table) 
SYSCAT.DBAUTH
SYSCAT.TABAUTH
SYSCAT.PACKAGEAUTH
SYSCAT.INDEXAUTH
SYSCAT.COLAUTH
SYSCAT.PASSTHRUAUTH
SYSCAT.SCHEMAAUTH

  More useful directory of the table 
  SYSCAT.COLUMNS: contains each line corresponding to the table or view out the definition of 
  SYSCAT.INDEXCOLUSE: contains each line contains all out 
  SYSCAT.INDEXES: contains each line corresponding to the table or view the definition of each index 
  SYSCAT.TABLES: the creation of each table, view, alias of his counterparts 
  SYSCAT.VIEWS: created each of which corresponds view or a few lines of his party 

  The only data maintained by the index of: CREATE UNIQUE INDEX INDEXNAME ON TABLE (COLUMN) 
  Eliminate duplication line: SELECT DISTINCT COLUMN FROM TABLE 

  3, DB2 some time on the function of the Year by the current time, months, days, hours, etc.: 
  YEAR (current timestamp) 
  MONTH (current timestamp) 
  DAY (current timestamp) 
  HOUR (current timestamp) 
  MINUTE (current timestamp) 
  SECOND (current timestamp) 
  MICROSECOND (current timestamp) 

  Each had at the time the date and time 
  DATE (current timestamp) 
  TIME (current timestamp) 

  About the time some calculation: 
  current date + 1 YEAR 
  current date + 3 YEARS + 2 MONTHS + 15 DAYS 
  current time + 5 HOURS – 3 MINUTES + 10 SECONDS 

  Between two dates calculated the number of days: 
  days (current date) – days (date ('1999-10-22 ')) 

  Ms by removing the current time: 
  CURRENT TIMESTAMP – MICROSECOND (current timestamp) MICROSECONDS 

  Time will be converted into a string: 
  char (current date) 
  char (current time) 
  char (current date + 12 hours) 

  Will be converted into a string: 
  TIMESTAMP ('2002-10-20-12 .00.00.000000 ') 
  TIMESTAMP ('2002-10-20 12:00:00 ') 
  DATE ('2002-10-20 ') 
  DATE ('10 / 20/2002 ') 
  TIME ('12: 00:00 ') 
  TIME ('12 .00.00 ') 

  Note: DB2 in the order in the editor can enter SQL statements and DB2 in the internal order.    To display the current time, can not be entered into the current time, this can only be invoked in the SQL language, in order to show the following: 
  1) VALUES (current time) 
  2) SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1 
  SQL SERVER2000 in this and not the same, in the SQL SERVER2000 can enter Getdate () by time, can, can also be used in SQL statements. 

  4, N of all to return to the previous data in SQL SERVER2000 expressions used in the TOP N format, for example: SELECT TOP 10 CARDNO FROM CARD 
  DB2 in the use of fetch first N rows only format, for example: SELECT CARDNO FROM SEALCARD fetch first 10 rows only 

  5, the function of the use of view the system function: SELECT * FROM SYSibm.sysfunctions; 
  For example: ABS (-89) can be used as input to SQL value, but in order to show that function in the editor if the results can be used the following methods: 
  1) SELECT ABS (-89) FROM SYSIBM.SYSDUMMY1; 
  2) VALUES ABS (-89); 

  6, in storage in DB2 storage process development, we can use the DB2 comes with a lot of tools, such as the development center, the control centre, and so on.    However, sometimes using a script to give developers greater flexibility and higher efficiency. 
  At the beginning of the development of a new or modified an existing storage process, we will normally carry out some preparatory work for the following: 
  1. View the storage process will use the table of the table structure, field types, examples of relevant indexes and data. 
  2. See related storage process or user-defined functions (UDF) of the definition. 
  3. Find invalid storage process and generate binding statement. 
  4. If a table has changed, view dependent on the table of all views, stored procedures and user-defined functions (UDF) 
  While this information can be provided through DB2 development tools and management tools available, but through the script can be more quickly to obtain the necessary information and can be repeated. 

  Using a script accomplish the above tasks, the key is to understand and use the system DB2 table.    A brief look back at the table and DB2 system view: 
  1. Syscat.routines: store all the stored procedures and user-defined functions (UDF) information.    One routinename field for the storage process or user-defined functions (UDF) the name, routinetype field that the record is that the storage process (P) or user-defined functions (F), lib_id field for storage after the formation process of compiling The package serial number, origin field that the storage process or user-defined functions of the source (Q that is composed of SQL, E that is user-defined and external), valid field that the storage process or user – Whether the definition of function effectively, if Q is not the origin field, the field is empty. 
  2. Syscat.packages: store all the bundled package.    One pkgname that package name, valid field that the package is legitimate. 
  3. Syscat.packagedep: storage on the package of dependence.    One pkgname field that package were, btype field that was dependent on the type of object, bname field that was dependent on the object name. 
  4. Syscat.routinedep: storage on the procedure (routine) dependence.    One routinename field that procedures were, btype field that was dependent on the type of object, bname field that was dependent on the object name. 
  5. Syscat.viewdep: storage on the view of dependence.    Viewname field of view were expressed, btype field that was dependent on the type of object, bname field that was dependent on the object name. 

  Review and understand the above chart and view, we can easily create a script to complete the development of the above-mentioned storage process to do the preparatory work. 
  1.    See the table structure, field types, examples of relevant indexes and data while we can get for sysibm.systables table table structure, but there is a more convenient way to access table structure, namely the use of db2look tools.    The tool can generate to create the table and the related index DDL.    If we want to obtain the designated specified in the table of the database structure and data before 20 as a reference, the following can be prepared script viewtbl.cmd, into parameters for the database name and table name. 
  @ echo —————— DDL of table% 2 and related index (ex) —————— 
  @ db2look-d% 1-t% 2-e 
  @ echo —————— fisrt 20 rows in table% 2 —————— 
  @ db2 select * from% 2 fetch first 20 rows only 

  2.    Show has been in existence during the storage and user-defined functions (UDF) of the definition, the results will be deposited automatically open the file and the outcome document. 
  Syscat.routines can do a simple query in the table to achieve the script viewrtn.cmd. 
  @ db2 SELECT text FROM SYSCAT.ROUTINES WHERE ROUTINENAME = upper ( '% 1')>% 1.sql 
  @ start% 1.sql 

  3.    See all the storage process is invalid and generate binding statement to delete stored procedure invoked the table will cause the storage process is invalid.    Invalid process can be used for storage and syscat.packages syscat.routines methods were: 
SELECT
  RTRIM (r.routineschema) | | '.' | | RTRIM (r.routinename) AS spname, 
  RTRIM (r.routineschema) | | '.' | | 'P' | | SUBSTR (CHAR (r.lib_id +10000000), 2) AS pkgname 
FROM
  SYSCAT.routines r 
WHERE
  r.routinetype = 'P' 
  AND ( 
  (r.origin = 'Q' AND r.valid! = 'Y') 
  OR EXISTS ( 
  SELECT 1 FROM syscat.packages 
  WHERE pkgschema = r.routineschema 
  AND pkgname = 'P' | | SUBSTR (CHAR (r.lib_id +10000000), 2) 
  AND valid! = 'Y' 
)
)
  ORDER BY spname 
  Attention should also query syscat.routines and syscat.packages table, because the package fails syscat.routines in value may still be valid for Y. 
  To automatically generated re-binding statement, simply rewrite the SQL into both invalidSP.cmd: 
  @ echo off 
  db2 "SELECT '@ db2 rebind package' | | RTRIM (r.routineschema) | | '.' | | 'P' | | SUBSTR (CHAR (r.lib_id +10000000) 2) 
  | | 'Resolve any' FROM SYSCAT.routines r WHERE r.routinetype = 'P' AND 
  ((r.origin = 'Q' AND r.valid! = 'Y') OR 
EXISTS
  (SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P' | | 
  SUBSTR (CHAR (r.lib_id +10000000), 2) AND valid! = 'Y')) "> rebindsp.bat 

  4.    Show a table on which the views, stored procedures and user-defined functions (UDF) 
  The use of the system view, it is easy to write the script: 
  @ echo off 
  echo — dependent SPs — 
  db2 "select proc.procschema, proc.procname 
  from syscat.routines r, syscat.procedures proc, syscat.packagedep pdep 
  where pdep.bname = upper ( '% 2') and pdep.bschema = upper ( '% 1') 
  and r.specificname = proc.specificname AND pdep.pkgname = 'P' | | SUBSTR (CHAR (r.lib_id +10000000), 2) " 

  echo — dependent UDF — 
  db2 select routineschema, routinename from syscat.routinedep where bschema = upper ( '% 1') 
  and bname = upper ( '% 2') and btype = 'T' order by bname 

  echo — dependent view — 
  db2 select viewschema, viewname from syscat.viewdep where bschema = upper ( '% 1') 
  and bname = upper ( '% 2') and btype = 'T' order by bname 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Tags: