Scribd
Upload
83 views

Data Types and Runsqlstm

This document discusses using RUNSQLSTM to execute SQL statements stored in a source physical file member. It covers creating a source physical file and member, adding SQL statements to the member with proper syntax, and executing RUNSQLSTM to run the statements and store results in a spooled file.

Uploaded by

sathis_nsk
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
83 views

Data Types and Runsqlstm

This document discusses using RUNSQLSTM to execute SQL statements stored in a source physical file member. It covers creating a source physical file and member, adding SQL statements to the member with proper syntax, and executing RUNSQLSTM to run the statements and store results in a spooled file.

Uploaded by

sathis_nsk
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 20

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:
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
Create a new Customer table
with a unique Customer Code
using a UDF
Label on
• Table or view – description
• Column
– Column Heading (20 characters per line)
– Text description
Authorization Terms
• See Chapter 9, figure 9.1 from text book
Object Ownership
• WRKOBJOWN
– Displays all objects that you own
• The owner automatically has *ALL
authority to an object
• Every object must have an owner
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
EDTOBJAUT

Edit Object Authority


See figures 9.3 and 9.4a in
text book
SQL Authorities
• Table or view
• Distinct type
• Stored procedure of UDF
• SQL Package

not collections or libraries


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
Grant Read and Update authority
to DA544A40 for the table
Customer
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
Grant Execute
• Grants another use the ability to execute a
UDF
GRANT EXECUTE
ON FUNCTION EFFECTIVERATE
TO DA544A40

• REVOKE EXECUTE removes the


authority
RUNSQLSTM
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)
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 ‘;’
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
Use WRKSPLF to view the
resulting Spooled File

You might also like