Front Cover: What's New in IBM I 7.3 and IBM POWER8 Systems

V11.
cover
Front cover
Course Exercises Guide
What’s New in IBM i 7.3 and IBM POWER8
Systems
Course code OS020G ERC 1.0
September 2016 edition
Notices
This information was developed for products and services offered in the US.
IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative
for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not
intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or
service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate
and verify the operation of any non-IBM product, program, or service.
IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this
document does not grant you any license to these patents. You can send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive, MD-NC119
Armonk, NY 10504-1785
United States of America
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do not allow disclaimer
of express or implied warranties in certain transactions, therefore, this statement may not apply to you.
This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein;
these changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s)
and/or the program(s) described in this publication at any time without notice.
Any references in this information to non-IBM websites are provided for convenience only and do not in any manner serve as an
endorsement of those websites. The materials at those websites are not part of the materials for this IBM product and use of those
websites is at your own risk.
IBM may use or distribute any of the information you provide in any way it believes appropriate without incurring any obligation to you.
Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other
publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other
claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those
products.
This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible,
the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to
actual people or business enterprises is entirely coincidental.
Trademarks
IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corp., registered in many
jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM
trademarks is available on the web at “Copyright and trademark information” at www.ibm.com/legal/copytrade.shtml.
© Copyright International Business Machines Corporation 2016.
This document may not be reproduced in whole or in part without the prior written permission of IBM.
US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
V11.0
Contents
TOC
Contents
Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iv
Exercises description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v
Exercise 1. IBM i Access Client Solutions: Explore the latest features . . . . . . . . . . . . . . . . . . . . 1-1
Part 1: Launching IBM i Access Client Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2
Part 2: Check for updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-3
Part 3: Integration with Navigator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-3
Part 4: Launch Run SQL Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4
Part 5: Insert from examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-5
Part 6: Other features of Run SQL Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-7
Part 7: Other Features of Access Client Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-9
Exercise 2. DB2 for i temporal tables and auto-generated values . . . . . . . . . . . . . . . . . . . . . . . . 2-1

Part 1: Creating a temporal table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2
Part 2: Writing time based queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-6
Part 3: Answers to lab questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-7
Exercise 3. Authority Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-1

Part 1: Authority Collection data: Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2
Part 2: A simple example: DSPPFM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2
Part 3: File System objects: Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3
Part 4: Adopted Authority: Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3
Part 5: Group profile: Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3
Part 6: Cleanup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3
© Copyright IBM Corp. 2016 iii

Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
V11.0
Trademarks
TMK
Trademarks
The reader should recognize that the following terms, which appear in the content of this training
document, are official trademarks of IBM or other companies:
IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business
Machines Corp., registered in many jurisdictions worldwide.
The following are trademarks of International Business Machines Corporation, registered in many
jurisdictions worldwide:
AIX® DB™ DB2®
developerWorks® DRDA® DS8000®
EnergyScale™ Express® FlashCopy®
FlashSystem™ HyperSwap® IBM Connections™
IBM Watson™ Insight™ Lotus®
Notes® Power Systems™ Power®
PowerHA® PowerVM® PowerVP™
POWER6® POWER7+™ POWER7®
POWER8® PurePower System™ Rational®
Redbooks® Redpaper™ System i®
System Storage® SystemMirror® WebSphere®
400®
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
Windows is a trademark of Microsoft Corporation in the United States, other countries, or both.
Java™ and all Java-based trademarks and logos are trademarks or registered trademarks of
Oracle and/or its affiliates.
VMware and the VMware "boxes" logo and design, Virtual SMP and VMotion are registered
trademarks or trademarks (the "Marks") of VMware, Inc. in the United States and/or other
jurisdictions.
Social® is a trademark or registered trademark of TWC Product and Technology, LLC, an IBM
Company.
Other product and service names might be trademarks of IBM or other companies.
© Copyright IBM Corp. 2016 iv

V11.0
Exercises description
pref
Exercises description
This course includes the following exercises:
• Exercise 1: IBM i Access Client Solutions: Explore the latest features
• Exercise 2: DB2 for i temporal tables and auto-generated values
• Exercise 3: Authority Collection
In the exercise instructions, you can check off the line before each step as you complete it to track
your progress.
Most exercises include required sections, which should always be completed. It might be
necessary to complete these sections before you can start later exercises. If you have sufficient
time and want an extra challenge, some exercises might also include optional sections that you can
complete.
© Copyright IBM Corp. 2016 v

V11.0
Exercise 1. IBM i Access Client Solutions: Explore the latest features
EXempty
Exercise 1. IBM i Access Client

Solutions: Explore the latest
features
Estimated time
00:45
Overview
IBM i Access Client Solutions (ACS) has been enhanced with many new features. This lab exercise
reviews the latest enhancements.
Throughout this lab exercise, the instructions will refer to the IBM i Lab partition that you will be
interacting with.
Objectives
At the end of the exercise, you should be able to:
• Launch Access Client Solutions
• Launch Run SQL Scripts
• Use Run SQL Scripts to explore IBM i Services
• Explore the integrated file system tasks
• Explore printed output
© Copyright IBM Corp. 2016 1-1

V11.0
EXempty
Exercise instructions
IBM i Access Client Solutions

IBM i Access Client Solutions provides a platform independent interface that consolidates the most
common tasks for using and managing your IBM i system. One of the more recent enhancements
for Access Client Solutions is Run SQL scripts. This lab introduces ACS and Run SQL scripts so
you are familiar with these functions for additional lab exercises later in this course.
Part 1: Launching IBM i Access Client Solutions

IBM i Access Client Solutions has been pre-loaded on your lab workstation and a system configuration
for the IBM i partition has already been set up for you. If you are not familiar with how to do these
tasks, the Getting Started guide found on the IBM i Access Client Solutions web site has extensive
documentation on set up and configuration. You can also find the Getting Started link from ACS help.
http://www-03.ibm.com/systems/power/software/i/access/solutions.html
ftp://ftp.software.ibm.com/as400/products/clientaccess/solutions/GettingStarted_en.html
The course presentation materials covered much of the set up considerations.
__ 1. Double click the Access Client Solutions icon on your workstation desktop to launch the product.
__ 2. You will see a window similar to the following:
You will note there is already a system configuration set up for the lab server. You can see the
IP address in the System drop-down list.

V11.0
EXempty
Part 2: Check for updates
IBM i Access Client Solutions is updated frequently. To make it easier to determine if there is a
more recent version, a Check for Updates feature has been added.
__ 3. Click on the Help menu item. the drop down now has a Check for Updates option.
__ 4. Click Check for Updates. This will quickly tell you whether updates are available.
Part 3: Integration with Navigator

IBM Navigator for i (the web console) is integrated with Access Client Solutions. To demonstrate
this integration, we’ll go through a few simple steps.
__ 5. Bring up a 5250 session. You will see a window appear to prompt to log into the system; log
in with your student ID and password. You will see the 5250 sign on screen appear. You do
not need to sign on to the 5250 session; you can simply minimize that window.
__ 6. Back at the ACS main window, select the Navigator for i task.
__ 7. Accept the security warnings; you will need to allow the browser to proceed to the web site.
__ 8. The Navigator main window will display and you will see the dashboard. Note that you did
not have to log into Navigator.
__ 9. Minimize the Navigator window.
__ 10. Minimize the Navigator requests window.

V11.0
EXempty
Part 4: Launch Run SQL Scripts
Run SQL scripts provides an interface to run SQL statements that was previously only in the
System i Navigator client.
__ 11. From the main IBM i Access Client Solutions window, take Run SQL Scripts.
__ 12. You should not have to sign in again since you did that when you started the 5250 emulator.
__ 13. You will now see the Run SQL Scripts panel appear. You should see that it automatically
connected to the lab IBM i partition.
You should see a message appear in the lower half of the Run SQL Scripts window that you are
connected to the relational database on the lab system and you will also see the fully qualified
job name of the database server job (QZDASOINIT) your requests will run in.
__ 14. You can compose your SQL statement in the top portion of the window. This lab will use
various IBM i Services as examples for SQL requests.

V11.0
EXempty
__ 15. Type in the following SQL statement:
select * from QSYS2.LIBRARY_LIST_INFO
__ 16. From the Run menu, select to run the selected statement.
__ 17. Review the results. It should look something like the following:
__ 18. Try a few additional simple examples and review the results:
select * from QSYS2.SYSTEM_STATUS_INFO
select * from QSYS2.SYSTMPSTG
select * from QSYS2.JVM_INFO
Part 5: Insert from examples

If you are knowledgeable about SQL, you can create more complex select statements and
queries. However, many users are not SQL experts. The IBM i Access Client Solutions Run SQL
Scripts function provides many examples to start from so you do not have to create the SQL
statements from scratch.

V11.0
EXempty
__ 19. From the Edit menu item, take the Insert from Examples… option.
__ 20. A new window, Examples will open. From the Examples drop down, you will find IBM i
Services at the very top of the list.

V11.0
EXempty
__ 21. There are many IBM i services to choose from.
__ 22. Select Storage – Temporary storage consumption, by active jobs, then click Insert.
__ 23. Click on the SQL statement that was inserted to have the cursor be in the statement and
then take Run à Selected to run the service. Review the results.
__ 24. Explore other example IBM i services.
Part 6: Other features of Run SQL Scripts

This section reviews some miscellaneous features of Run SQL Scripts.
__ 25. You can easily view the job and job log for the QZDASOINIT database server job where
your queries are running. Click on the View menu item and review the options that are
displayed.

V11.0
EXempty
Feel free to take any of these menu items and explore results.
__ 26. If you view the job log, note that you can double click on a message and see the full
second-level message text.
__ 27. If you select “Job Details” you will automatically be connected to a Navigator browser
window where you will find the “Work with a Job” task for your QZDASOINIT job.
__ 28. Review the Options menu. You will see there are various options to select; for example,
you can use a double-click of your mouse to run an SQL statement.

V11.0
EXempty
__ 29. Review the Connection menu.
__ 30. Take the option JDBC Settings… and review all the settings that are available.
__ 31. Visual Explain has been added to Access Client Solutions. Select the last SQL Statement
you ran and then select VisualExplain > Explain.
__ 32. You will see Visual Explain is launched in its own window. Close all the windows related to
Visual Explain and Run SQL scripts.
Part 7: Other Features of Access Client Solutions

This section reviews some miscellaneous features of Access Client Solutions.
__ 33. From the main ACS window, launch Integrated File System. This will bring up a window
where you can drill into the IFS.

V11.0
EXempty
__ 34. Under the Actions menu item, you will find the new features to work with files in the IFS.
__ 35. Exit the IFS task.

__ 36. From the main ACS window, launch Printer Output. You will not see any printed output, so
select Edit > Filter.

V11.0
EXempty
__ 37. Select to display output from all users and then click OK. You should see at least one
spooled file.
__ 38. Right click on that spooled file and review the options that are available. You will observe
that ACS has the ability to manipulate spooled files. Not only can you view them, but you
can hold, release, move, and delete them.
__ 39. Close all windows.

This concludes the exercise.
End of exercise

V11.0
Exercise 2. DB2 for i temporal tables and auto-generated values
EXempty
Exercise 2. DB2 for i temporal tables

and auto-generated values
Estimated time
01:00
Overview
IBM i 7.3 included many DB2 for i enhancements including support for temporal tables. This lab
contains two modules. The first highlights how to turn an existing table into a temporal table. The
second illustrates how to write time based queries to access a temporal table. This lab uses the
ACS Run SQL Scripts support which was already covered in the course lectures and labs.
Note that this lab does not show a full implementation of temporal tables. It is important to seek
guidance from IBM before implementing this new functionality in a production environment.
Throughout this lab exercise, the instructions will refer to the IBM i Lab partition that you will be
using.
Objectives
• Convert an existing table to a temporal table
• Write time based queries against a temporal table

V11.0
EXempty
Temporal Tables
The following will guide you through the process of creating and using temporal tables. Some SQL
statements will contain the value ??? which indicates that you need to complete the syntax of the
SQL statement using the information in the course lecture charts. Note that your replacement for
??? may consist of a single keyword or an entire clause in SQL.
The answers to the questions in the lab exercise are found in Part 3. Answers to lab questions.
Part 1: Creating a temporal table

This exercise will use ACS Run SQL scripts and a template script which is located on your desktop.
__ 1. On your workstation, launch ACS Run SQL scripts using your assigned user profile and
password to connect to the lab partition.

V11.0
EXempty

V11.0
EXempty
__ 2. Once you have connected, use the Run SQL Scripts File -> Open to open the file named
DB2 Lab 1 in the directory OS020G and subdirectory DB2.
__ 3. Execute the following SQL statements after replacing <xx> with your two digit team
number. The first statement drops the schema for your team. If this statement fails, move
to the next statement. CREATE SCHEMA will create an SQL schema corresponding to
your team name. Remember a schema is implemented as an IBM i library containing some
additional objects. SET SCHEMA establishes a default schema for your session. Note that
this statement must be repeated anytime you reconnect to the server.
DROP SCHEMA OS20T<xx> ;
CREATE SCHEMA OS20T<xx> ;
SET SCHEMA OS20T<xx> ;
__ 4. Now run the following statement to create the address table. It will replace any existing
instance of the table and delete any rows in the previous instance. The table being created
will be a copy of a table provided for the course.
CREATE OR REPLACE TABLE address
AS (SELECT * FROM OS020GDB2.address) WITH DATA
ON REPLACE DELETE ROWS ;
__ 5. Run this SQL statement to look at the definition and contents of the address table you just
created.
SELECT * FROM address ;

V11.0
EXempty
In the following steps, we’ll convert this table to a temporal table. Remember that you need to
replace ??? with the correct syntax from the lecture materials.
__ 6. Remember that temporal tables need columns defined to indicate the “birth” and “death” of
row images plus a column needed to manage transaction boundaries under commitment
control. Complete and run the following SQL statement to add the necessary columns to
the employee table.
ALTER TABLE address
ADD COLUMN starting_date ???
GENERATED ALWAYS AS ???
ADD COLUMN ending_date ???
GENERATED ALWAYS AS ???
ADD COLUMN transaction_id ???
GENERATED ALWAYS AS TRANSACTION START ID
ADD PERIOD SYSTEM_TIME (???, ???) ;
__ 7. Complete and run the following SQL statements to create the address_history table and to
configure the temporal table.
CREATE OR REPLACE TABLE address_history ??? address
ALTER TABLE address
ADD ??? TABLE address_history ;
__ 8. Run the following INSERT, UPDATE, and DELETE statements.
INSERT INTO address
VALUES (703, 'Creative Entertainment', '1100 Leisure Way', 'Miramar',
'FL', '33027');
UPDATE address
SET address='Havenwood Drive' city = 'Milwaukee' state='WI'
postal_code='xxxxx'
WHERE address_id = 644 ;
DELETE FROM address WHERE address_id = 907 ;
__ 9. Run the two following SELECT statements to look at the contents of the address and
address_history tables. Answer the questions that follow.
SELECT * FROM address ;
SELECT * FROM address_history ;
Questions
Answer the following questions:

• Where do you see the row corresponding to address_id 703, Creative Entertainment?
• What values does it show for starting_date and ending_date?
• Do either of the tables show an entry corresponding to address_id 644, Richter Printing?

V11.0
EXempty
• What values do you see for starting_date and ending_date?
• Where do you see values corresponding to address_id 907, Xenon Research?
• What value do you see for the ending_date?
Part 2: Writing time based queries

This exercise will again use ACS Run SQL scripts and a template script located on your desktop.
__ 10. On your workstation, launch ACS Run SQL scripts using your assigned user profile and
password to connect to the lab partition. If it is already open, you can skip this step.
__ 11. Once you have connected, use the Run SQL Scripts File -> Open to open the file named
DB2 Lab 2 in the directory OS020G and subdirectory DB2.
__ 12. Execute the following SQL statement. SET SCHEMA establishes a default schema for your
session. Note that this statement must be repeated anytime you reconnect to the server.
This schema contains a temporal table that you will be querying.
SET SCHEMA OS020GDB2;
__ 13. Modify and run the following SQL statement to determine employee 30’s salary on February
28, 2015.
SELECT emp_id, emp_name, salary
FROM salary FOR ??? '2015-02-28 00:00:00.000000000000'
WHERE emp_id = '000030'
__ 14. What was the employee’s salary at that point in time? _________________
__ 15. Modify and run the following SQL statement to determine employee 30’s salary history
between February 28, 2015 and the present time.
SELECT emp_id, emp_name, effective_start, effective_end, salary
FROM salary FOR ??? '2015-02-28 00:00:00.000000000000'
??? CURRENT_TIMESTAMP
ORDER BY effective_start ;
__ 16. How many different salary values did you observe in the data? _________________
__ 17. What other changes if any did you observe? _____________________________
__ 18. Modify and run the following SQL statement to determine employee 30’s salary history as of
September 30, 2015. Do not change the SELECT statement.
SET CURRENT ??? '2015-09-30 23:59:59.000000000000' ;
SELECT emp_id, emp_name, salary FROM salary WHERE emp_id = '000030' ;
__ 19. What was the employee’s salary at that point in time? _________________
__ 20. Run this statement to reset your environment.
SET CURRENT TEMPORAL SYSTEM_TIME NULL;

V11.0
EXempty
Part 3: Answers to lab questions
Answers to questions in Part 1: Creating a temporal table
Completed SQL statements are also in the folder OS020GDB2/Reference.
Question in Part 1 step 7:
Remember that temporal tables need columns defined to indicate the “birth” and “death” of row
images plus a column needed to manage transaction boundaries under commitment control.
Complete and run the following SQL statement to add the necessary columns to the employee
table.
ALTER TABLE address
ADD COLUMN starting_date TIMESTAMP(12) NOT NULL
GENERATED ALWAYS AS ROW BEGIN
ADD COLUMN ending_date TIMESTAMP(12) NOT NULL
GENERATED ALWAYS AS ROW END
ADD COLUMN transaction_id TIMESTAMP(12)
GENERATED ALWAYS AS TRANSACTION START ID
ADD PERIOD SYSTEM_TIME (starting_date, ending_date) ;

Complete and run the following SQL statement to create the address_history table and to
configure them as a temporal table. You can ignore any errors on the DROP TABLE statement.
CREATE OR REPLACE TABLE address_history LIKE address
ALTER TABLE address
ADD VERSIONING USE HISTORY TABLE address_history ;
Questions in step 10:
• Where do you see the row corresponding to address_id 703, Creative Entertainment?
It appears only in the address table.
• What values does it show for starting_date and ending_date?
Starting_date shows today’s date and the time when the INSERT statement was run.
Ending_date shows 9999-12-30-00.00.00.000000000000.
• Do either of the tables show an entry corresponding to address_id 644, Richter Printing?
Richter Printing is in both the address and address_history tables.
• What values do you see for starting_date and ending_date?
In the address table, the starting date is today’s date and time when the UPDATE statement
was run. The ending date shows the arbitrary far future value of 9999-12-30. In the
address_history table, the starting date is the arbitrary far past value of 0001-01-01. The
ending date is today’s date and time when the UPDATE statement was run.
• Where do you see values corresponding to address_id 907, Xenon Research?
The associated data is not contained in the address table. It appears in the address_history table.
• What value do you see for the ending_date?
The ending date shows today’s date and time when the DELETE statement was run.

V11.0
EXempty
Answers to questions in Part 2: Writing time based queries
Completed SQL statements are in the folder OS020GDB2/Reference.
Modify and run the following SQL statement to determine employee 30’s salary on February 28,
2015.
SELECT emp_id, emp_name, salary
FROM salary FOR SYSTEM_TIME AS OF '2015-02-28 00:00:00.000000000000'
WHERE emp_id = '000030' ;
What was the employee’s salary at that point in time? 39780.00
Modify and run the following SQL statement to determine employee 30’s salary history between
February 28, 2015 and the present time
SELECT emp_id, emp_name, effective_start, effective_end, salary
FROM salary FOR SYSTEM_TIME FROM '2015-02-28 00:00:00.000000000000'
TO CURRENT_TIMESTAMP
ORDER BY effective_start ;
How many different salary values did you observe in the data? Three values.
They were 39780.00, 42564.60, and 43841.53.
What other changes if any did you observe?

Sally A. Kwan changed her name to Sally A. Kwan-Lee as of April 4, 2015.
Modify and run the following SQL statement to determine employee 30’s salary history as of
September 30, 2015. Do not change the SELECT statement.
SET CURRENT TEMPORAL SYSTEM_TIME '2015-09-30 23:59:59.000000000000' ;
SELECT emp_id, emp_name, salary FROM salary WHERE emp_id = '000030' ;
What was the employee’s salary at that point in time? 42564.60
End of exercise

V11.0
Exercise 3. Authority Collection
EXempty

Estimated time
00:45
Overview
This exercise gives the student an opportunity to work with Authority Collection.
Objectives
• Work with Authority Collection to collect data

V11.0
EXempty
Part 1: Authority Collection data: Example
__ 1. On your workstation, open IBM i Access Client Solutions (ACS). Under the General tab,
select 5250 Emulator (twice) to bring up two (2) 5250 sessions.
__ 2. On the first 5250 session, signon as test user ACUSERxx where xx is your student number.
Your password is the same as your test user ACUSERxx.
__ 3. On the second 5250 session, signon as admin user ACADMNxx where xx is your student
number. Your password is the same as your test user ACADMNxx.
__ 4. On the second 5250 session (User ACADMNxx), enter the following command where xx is
your student number:
STRAUTCOL USRPRF(ACUSERxx) LIBINF(*ALL) INCFSOBJ(*ALL) DLTCOL(*YES)
__ 5. On the first 5250 session (User ACUSERxx), enter the following command:
CALL PGM(ACTESTLIB/AUTCOL)
__ a. On “Display Physical File Member” panel, press F3 to exit.
__ b. On “Display Data Area” panel, press F3 to exit.
__ 6. From IBM i Access Client Solutions (ACS) Database tab, select Run SQL Scripts.
__ 7. From the Run SQL Scripts session, enter the following where xx is your student number:
SELECT * FROM qsys2.authority_collection where user_name = ‘ACUSERxx’
__ a. Click on Run then Selected at top of session.
__ 8. Authority collection data is displayed for user ACUSERxx.
Part 2: A simple example: DSPPFM

__ 9. On the second 5250 session (User ACADMNxx), enter the following two commands where
xx is your student number:
ENDAUTCOL USRPRF(ACUSERxx)
STRAUTCOL USRPRF(ACUSERxx) LIBINF(*ALL) INCFSOBJ(*ALL) DLTCOL(*YES)
DSPPFM FILE(ACTESTLIB/ACSRCFILE) MBR(ACMBR)
__ a. On “Display Physical File Member” panel, press F3 to exit.
SELECT authorization_name, system_object_name, system_object_schema,
system_object_type,required_authority,detailed_required_authority,current_authority,detai
led_current_authority,authority_source
FROM qsys2.authority_collection where user_name = 'ACUSERxx' and (system_object_name =
'ACSRCFILE' or system_object_name = 'DSPPFM')

__ 12. Authority collection data is displayed for user ACUSERxx, objects ACTESTLIB/ACSRCFILE
and QSYS/DSPPFM.

V11.0
EXempty
Part 3: File System objects: Example
__ 13. On the first 5250 session (User ACUSERxx), enter the following command where xx is your
student number:
EDTF STMF(‘ACADMINDIR1/ACSTMFxx’)
__ a. On Edit File panel. Press F3 to exit.
SELECT * FROM qsys2.authority_collection where user_name = ‘ACUSERxx’ and
system_object_name IS NULL
__ 15. Authority collection data is displayed for user ACUSERxx, all file system objects.
Part 4: Adopted Authority: Example

__ 16. On the first 5250 session (User ACUSERxx), enter the following command where xx is your
student number:
CALL PGM(ACTESTLIB/AUTCOLADP) PARM(‘xx’)
(system_object_name = ‘ACADPEXCxx’ or system_object_name = ‘ACADPALLxx’)
__ 18. Authority collection data is displayed for user ACUSERxx, entries for programs
ACTESTLIB/ACADPEXCxx and ACTESTLIB/ACADPALLxx.
Part 5: Group profile: Example

DSPDTAARA DTAARA(ACTESTLIB/ACGRPTEST)
__ a. On the “Display Data Area” panel, press F3 to exit.
__ 20. From the Run SQL Scripts session, enter the following:
system_object_name = ‘ACGRPTEST’
__ 21. Authority collection data is displayed for user ACUSERxx, data area ACGRPTEST.
Part 6: Cleanup
__ 22. From the Run SQL Scripts session, click on File then Exit at top. In Inquiry Message pop-up
box asking “Save changes,” click No.
SIGNOFF

V11.0
EXempty
__ 24. On the second 5250 session (User ACADMNxx), enter the following command where xx is
your student number:
ENDAUTCOL USRPRF(ACUSERxx)
DLTAUTCOL USRPRF(ACUSERxx)
SIGNOFF
__ 25. Close both ACS 5250 sessions. In pop-up box asking “Save current config,” click on No.
End of exercise

V11.0
backpg
© Copyright International Business Machines Corporation 2016.

Front Cover: What's New in IBM I 7.3 and IBM POWER8 Systems

Uploaded by

Copyright:

Available Formats

Front Cover: What's New in IBM I 7.3 and IBM POWER8 Systems

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Front Cover: What's New in IBM I 7.3 and IBM POWER8 Systems

Uploaded by

Copyright:

Available Formats

V11.

Exercise 2. DB2 for i temporal tables and auto-generated values . . . . . . . . . . . . . . . . . . . . . . . . 2-1

Exercise 3. Authority Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-1

© Copyright IBM Corp. 2016 iii

© Copyright IBM Corp. 2016 iv

© Copyright IBM Corp. 2016 v

Exercise 1. IBM i Access Client

© Copyright IBM Corp. 2016 1-1

IBM i Access Client Solutions

Part 1: Launching IBM i Access Client Solutions

__ 2. You will see a window similar to the following:

© Copyright IBM Corp. 2016 1-2

Part 3: Integration with Navigator

© Copyright IBM Corp. 2016 1-3

© Copyright IBM Corp. 2016 1-4

Part 5: Insert from examples

© Copyright IBM Corp. 2016 1-5

© Copyright IBM Corp. 2016 1-6

Part 6: Other features of Run SQL Scripts

© Copyright IBM Corp. 2016 1-7

© Copyright IBM Corp. 2016 1-8

Part 7: Other Features of Access Client Solutions

© Copyright IBM Corp. 2016 1-9

__ 35. Exit the IFS task.

© Copyright IBM Corp. 2016 1-10

__ 39. Close all windows.

© Copyright IBM Corp. 2016 1-11

Exercise 2. DB2 for i temporal tables

© Copyright IBM Corp. 2016 2-1

Part 1: Creating a temporal table

© Copyright IBM Corp. 2016 2-2

© Copyright IBM Corp. 2016 2-3

© Copyright IBM Corp. 2016 2-4

Answer the following questions:

• What values does it show for starting_date and ending_date?

© Copyright IBM Corp. 2016 2-5

• Where do you see values corresponding to address_id 907, Xenon Research?

• What value do you see for the ending_date?

Part 2: Writing time based queries

© Copyright IBM Corp. 2016 2-6

Question in Part 1 step 8:

© Copyright IBM Corp. 2016 2-7

What other changes if any did you observe?

© Copyright IBM Corp. 2016 2-8

Exercise 3. Authority Collection

© Copyright IBM Corp. 2016 3-1

Part 2: A simple example: DSPPFM

__ a. Click on Run then Selected at top of session.

© Copyright IBM Corp. 2016 3-2

Part 4: Adopted Authority: Example

Part 5: Group profile: Example

© Copyright IBM Corp. 2016 3-3

© Copyright IBM Corp. 2016 3-4

© Copyright International Business Machines Corporation 2016.

You might also like