Earlier this year (2015) IBM announced a new certification for DB2 for z/OS - IBM Certified Application Developer - DB2 11 for z/OS. This certification is intended for application developers for DB2 for z/OS and thus may be appealing for individuals and organizations who are developing applications using DB2 for z/OS as their backend. The certification itself requires one test only - Test C2090-313: DB2 11 Application Developer for z/OS and a required pass score is 63% out of 60 questions within 75 minutes time frame.
If you continue reading you are probably willing to learn more or even start preparing for the test (depending on your role). The objectives of the test reveal the topics covered by the test, total of 10 areas are part of the test:
- Database Objects
- Data Retrieval & Manipulation
- Distributed Access to DB2
- Coding SQL in Application Programs
- Processing Options that affect program design
- Performance Considerations
- Units of work, concurrency, and checkpoint/restart
- Application Design & Lifecycle
- Access to Non-Structured Data
- Advanced Programming Topics
If you already passed some certifications like DB2 Fundamentals and DB2 Database Administrator, you will probably find some of the topics familiar. Similarly, if you are a regular developer for DB2 for z/OS, you should already have a solid knowledge of some of the topics. However, if you are willing to invest some time to educate yourself instead of just passing the test, you are probably going to study the topics in more details.
What is the first thing to start with? I would say calm down and look at the recent discussion at IDUG.org or go directly to a linkedin page where Chris Crone, IBM Distinguished engineer, expressed his approach to the test. OK, good news is that you are not supposed to read and learn every single manual from the top to the bottom, but you need to focus on some specific things, some of which you probably know a lot already.
What sources are available? To my knowledge, there are currently (as of June 2015) no certification guides available that you could use for your studies. The test preparation website mentions just the IBM knowledge center and some courses you could take. Also, there is a sample test you can pay for a trial.
This did not work for me, so I decided to collect all available information, where I could learn more about the fields of interests in order to be prepared for the certification. My primary resources were:
- IBM knowledge center for DB2 11,
- IBM manuals available for download, especially the Application Programming and SQL Guide and the SQL Reference,
- few redbooks for information management related to DB2, unfortunately some of them a bit outdated,
- and also some resources at IDUG.org
Still a lot of information and materials to read, isn’t it? What I did next was going through the individual topics and narrowing the materials as per the test objectives. Some of the items I already knew, some of them not, as usual in this world. What I would like to do in the next few short blogs is to share my links with some additional comments from the IDUG Content Committee, so that we can eventually discuss the topics and share the knowledge.
This blog covers the first objective - Database objects - of the certification test 313 as we discussed in the introduction. The topic itself is divided into several subcategories, which will follow below in this post. If you are working as a DBA this will probably be an easy part for you, but let’s start from the beginning.
A nice introduction to DB2 objects is contained within the Introduction to DB2 guide, section DB2 Data structures; while you can find some more advanced discussion in the administration guide, section Implementing your database design; and database objects from the application programming perspective in the Application programming guide, section Creating and modifying DB2 objects. Of course the SQL reference gives you the details for each individual option you can use, but I guess the test does not require you to remember everything.
OK, let’s move further and focus on the topics covered by this part.
Create and alter database demonstrating knowledge of database attributes
- I think that the important thing here is to understand the concept of a database in DB2 for z/OS, which is significantly different than on other platforms. While a database on other platforms has physical characteristics, database in DB2 for z/OS is more like a logical grouping of objects.
- A definition of a database can be found in the Introduction to DB2: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.intro/src/tpc/db2z_databases.dita?lang=en
- To learn more about creating and altering the database, follow the Administration guide: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_databaseimplementation.dita?lang=en, http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_alterdatabases.dita?lang=en, and http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_alterdatabaseoptions.dita?lang=en
- Attributes of the database (which are just few) are discussed in the SQL Reference, in CREATE DATABASE and ALTER DATABASE: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_createdatabase.dita?lang=en, http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_alterdatabase.dita?lang=en.
- If you are interested in the main differences between DB2 for z/OS and DB2 for LUW, see the following blog: http://www.idug.org/p/bl/et/blogaid=322
- In my opinion, this section is not just about the CREATE and ALTER DATABASE, but also about the other objects. So, I would say that you should also have a basic knowledge of the tablespaces and indexspaces as a database is just a collection of these objects. You can find the related information close to the database information as well.
Create and alter tables demonstrating knowledge of table attributes
- Tables are what the programmers usually work with. Again, there is an introduction to the tables in the Introduction to DB2, which also lists all the table types supported by DB2 for z/OS: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.intro/src/tpc/db2z_tables.dita?lang=en
- A nice overview of creating the tables, together with their attributes is discussed in the Application programming and SQL guide here: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_createtablesapp.dita?lang=en
- The information about creating and altering tables (I would focus on the base tables only) from the DBA’s perspective can be found in the Administration guide: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_tableimplementation.dita?lang=en, http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_altertables.dita?lang=en
- And as usual, details can be found in the SQL Reference: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_createtable.dita?lang=en, http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_altertable.dita?lang=en
- I would also recommend looking at the examples of the CREATE TABLE and ALTER TABLE in the SQL Reference.
Demonstrate knowledge of types, attributes, and purpose of indexes
- Indexes basically serve the following purposes - performance, uniqueness, and clustering (used to be used for partitioning as well) - as described here: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.intro/src/tpc/db2z_indexesforperformance.dita and here http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_indexesoncolumns.dita?lang=en
- the types of indexes are nicely categorized here: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.intro/src/tpc/db2z_typesofindexes.dita?lang=en
- Few guidelines how to create indexes can be found in the Administration guide: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_indeximplementation.dita?lang=en
- Some details about indexes and performance is written in the managing Performance guide: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.perf/src/tpc/db2z_evaluateindexesperf.dita?lang=en
- What attributes of an index can be altered are summarized here: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_alterindexes.dita?lang=en; what I especially found important here is how to change the padding of varying length columns http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_altervariyinglengthcolumns.dita?lang=en and how altering of clustering works http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_alterindexclusert.dita?lang=en
- The details about index creation and alteration can be found in the SQL Reference, again, I would recommend looking at the examples: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_createindex.dita?lang=en, http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_alterindex.dita?lang=en
Create table columns demonstrating knowledge and application of various data types
- DB2 for z/OS supports several data types. I found useful this diagram, which makes a nice overview of all the types (see Application programming and SQL guide): http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_columndatatype.dita?lang=en
- More details about every type can be found in the SQL reference: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_datatypesintro.dita?lang=en. For example you can find here the precision and maximum ranges of the types.
- You can see the similar information in the Introduction to DB2 as well: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.intro/src/tpc/db2z_datatypes.dita?lang=en
- The syntax rules for defining a column of a given type can be found for example by the description of CREATE TABLE: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_createtable.dita?lang=en
- The limits for each data type are also summarized in the Limits section in the SQL Reference: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_limits.dita?lang=en
Apply constraints to database tables and columns
- The following page lists the available constraints (unique, referential, check): http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.intro/src/tpc/db2z_constraintsintro.dita?lang=en I would add NOT NULL as well.
- the topic (especially CHECK and referential constraints) is being discussed in the Programming guide as well: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_waysdataintegrity.dita?lang=en
- and again CREATE TABLE description contains all the syntactical details: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_createtable.dita?lang=en
Correctly apply encoding schemes to tables
- I would start by reading the introduction to character conversion here: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.char/src/tpc/db2z_introcharconv.dita?lang=en, especially the section describing the available encoding schemes - ASCII, EBCDIC, and UNICODE - http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.char/src/tpc/db2z_encodingscheme.dita?lang=en, and the section describing the possibles consequences of character conversions: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.char/src/tpc/db2z_consequencecharconv.dita?lang=en
- The internationalization guide then describes how to specify CCSID for your application: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.char/src/tpc/db2z_specifyccsidapp.dita?lang=en
- The interesting topic is how DB2 performs the character conversion in set operations and concatenations, the description is here http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_characterconversioninsetoperations.dita?lang=en and http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_selectresultccsid.dita?lang=en
- Important to know is that there are default encodings schemes as described here: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.char/src/tpc/db2z_subsystemccsids.dita?lang=en
Manage sequences and identities on tables
- Identity columns and sequences share some similar ideas, but are completely different in the implementation and usage. While an identity column is associated with a table, a sequence is completely independent.
- This is how you can implement an identity column: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_identitycols.dita?lang=en. Also make sure to check the rules for inserting the data into an identity column http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_rulesidentitycolumn.dita?lang=en.
- Sequences have similar attributes (CACHE, CYCLE, START, INCREMENT, …), but are not associated with tables: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_sequenceobject.dita?lang=en, applications can use NEXT VALUE or PREVIOUS VALUE expressions to receive values from the sequences as can be seen here http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_referencesequenceobject.dita?lang=en.
- details of how to create identity columns are described in the SQL reference: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_createtable.dita?lang=en
- details about creating and altering sequences are in SQL Reference as well: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_createsequence.dita?lang=en, http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_altersequence.dita?lang=en
- The important here is also understanding the attributes of the identity columns and sequences (especially how the caching and cycling works), details can be found in the SQL Reference.
Create views and understand the best practices for the use of those views
- The Application programming guide discusses when to use views: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_defineview.dita?lang=en
- Administration guide adds some more details http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_viewimplementation.dita?lang=en, especially it is important how DB2 inserts and updates data through views with/without the CHECK OPTION http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.admin/src/tpc/db2z_howupdateinviews.dita?lang=en
- Details are provided in the SQL reference, http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sql_createview.dita?lang=en for example it defines read-only views.