1 / 20

Data Types and RunSQLSTM

Data Types and RunSQLSTM. Agenda. Lab 1 demo this week Bring your lab notes! Create your own Data Types Label on Authority RunSQLstm. Create your own Data Types. Also known as User-Defined Types (UDTs) Used to create specific versions of standard data types Eg:

ndebra
Download Presentation

Data Types and RunSQLSTM

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Types and RunSQLSTM

  2. Agenda • Lab 1 demo this week • Bring your lab notes! • Create your own Data Types • Label on • Authority • RunSQLstm

  3. Create your own Data Types • Also known as User-Defined Types (UDTs) • Used to create specific versions of standard data types • Eg: Create Distinct Type CustIDType as Dec(7, 0) with Comparisons Creates a data type called CustIDType that is a 7 digit decimal. The ‘with Comparisons’ allows the use of >, =, etc with this data type

  4. Create a new Customer table with a unique Customer Code using a UDF

  5. Label on • Table or view – description • Column • Column Heading (20 characters per line) • Text description

  6. Authorization Terms • See Chapter 9, figure 9.1 from text book

  7. Object Ownership • WRKOBJOWN • Displays all objects that you own • The owner automatically has *ALL authority to an object • Every object must have an owner

  8. If you’re not the owner • Then you can have • Explicit authority • Part of an authorization list • Belong to a group profile • Part of *PUBLIC

  9. EDTOBJAUT Edit Object Authority

  10. See figures 9.3 and 9.4a in text book

  11. SQL Authorities • Table or view • Distinct type • Stored procedure of UDF • SQL Package not collections or libraries

  12. SQL Authorities • Grant • Tables and views GRANT action1, action2, action3, etc ON table1, table2, view1, etc TO user1, user2, user3 • The ALL keyword can be used to allow authority to all actions • REVOKE removes authority, by action

  13. Grant Read and Update authority to DA544A40 for the table Customer

  14. Grant Usage • Grants usage of user defined distinct types to other users GRANT USAGE ON DISTINCT TYPE DOLLAR TO DA544A40 • Gives DA544A40 use of the distinct type DOLLAR • REVOKE USAGE revokes authority

  15. Grant Execute • Grants another use the ability to execute a UDF GRANT EXECUTE ON FUNCTION EFFECTIVERATE TO DA544A40 • REVOKE EXECUTE removes the authority

  16. RUNSQLSTM

  17. RUNSQLSTM • Executes a list of SQL statements which are stored in a Source Physical File • Each Statement must end in an ; (semi-colon) • Results from executing the RUNSQLSTM command are stored in a spooled file (WRKSPLF)

  18. Creating a Source Member for RUNSQLSTM • Create the source physical file (CRTSRCPF) • Clear your session (option 3 from session attributes) • Test all commands in that you want to include in your source member • Upon exit, type option 4 – Save Session in Source File • Enter the file, library and member parameters of the Change Source File screen • Edit the source physical file member using SEU or Websphere • Comments start with – (two dashes) • Commands must end with an ‘;’

  19. Executing SQL statements using RUNSQLSTM • If the SQL statements are stored in the Source PF, DA544A40/QSQLSRC and the source member RUNTST • RUNSQLSTM SRCFILE(LAURIN/QSQLSRC) SRCMBR(RUNTST) NAMING(*SQL) • Naming convention must be changed to *SQL if the sql convention, library.object is used

  20. Use WRKSPLF to view the resulting Spooled File

More Related