Data Henrik: quiz

Showing posts with label quiz. Show all posts

Thursday, August 3, 2017

Db2 Quiz: Oh what files!?

Today, it is time for another Db2-related quiz. Take a look at the screenshot below. What do you see, what is it?

Db2 Quiz

Need a hint? The quiz is related to my upcoming presentations at the IDUG and DB2 Aktuell conferences.

Wednesday, April 12, 2017

Are you ready for a new DB2 quiz? Here is a tricky question for you. It is about DB2 security, it is (kind of) hardware-related and deals with a relatively new DB2 feature. Curious...?

The following shows the output of a DB2 tool, shipped with your regular DB2 LUW these days:

Keystore Password:
Password successfully stashed to db2_pkcs11_pwd.sth

xxxxx completed sucessfully.

Have a clue? (The "xxxxx" is replacing the name of the db2 tool). If you know the answer, comment and also paste the link to the page in DB2 Knowledge Center describing that tool. I will publish the comments in the next few days.

Tuesday, January 10, 2017

DB2 Quiz for the Resource-Minded & IDUG EMEA 2017

Did you know...?

I hope you had a good start into 2017. I am already up and running and accomplished some important tasks. I submitted my presentation proposals for the IDUG DB2 Tech Conference 2017 in Lisbon, Portugal. The Call for Papers is still open until February 20th. The IDUG Conference is always a great place to learn new stuff and meet great people.

Speaking of learning new stuff. Do you know which DB2 function or procedure produced the following output on my system? As you may notice, I checked some DB2 system processes using SQL. The feature I am using is around since DB2 version 9.7.
If you have a guess, leave a comment or send an email.

MEMBER DB2_PROCESS_NAME DB2_PROCESS_ID       CPU_USER     CPU_SYSTEM
------ ----------------- -------------------- ------------ ------------
0      db2fmp           15234    6            7
0      db2vend (PD Vendo                15064            5            3
0    db2ckpwd 0                       15060            0            0
0      db2ckpwd 0                       15061            0            0
0      db2ckpwd 0                       15062            0            0
0      db2sysc 0                        15054           91          128
...

8 record(s) selected.

Thursday, December 18, 2014

Data and 2014: Security and privacy

some bits

With security and privacy being one of the top topics for data processing in this year (and probably the next year too), I wanted my last post for this year to be spot on. Here is some input for DB2, dashDB, or the SQLDB service on Bluemix, the output is for you:

values(decrypt_char(cast (x'08D030FFB804A5D560F6F794046C11F329C9004B80EC3159' as varchar(50) for bit data),'santaclaus'));

BTW: This is not a quiz... :)

Wednesday, July 30, 2014

DB2 Quiz: Which tool provides this output?

Another day, another DB2 quiz. Below is the partial output produced by a DB2 tool. Which tool is it? It is part of every DB2 LUW installation.

COLUMN NAME               : ID
Length of column name     : 2
Column id                 : 0
Col type {hex, type}      : 0x0001 , INTEGER
Col dist. type {hex, type}: 0x0018 , INTEGER
Column length             :      4
Blob length               : 4
Statistic offset          : 480
Column String Units       : N
Flags                     : 0x20000090
                             - SQLRG_MIXED
                             - SQLRG_NULLIND
Flags2                    : 0x00000000
Codepage                  : 0
Collation name            : NOT_APPLICABLE
Collation ID in hex       : B'FF FF FF FF FF FF FF FF
Length of user default    : 0
Logged                    : 0
Compact                   : 0
Inline Length             : 0
Datalink Features         : 000000
Ref. row type desc. offset: 0
No. unique values in col. : 2
Average column length     : 5
Expected Change Rate      : 0.000781
Percentage Encoded        : -1
Page Variance Ratio       : -1
Source column ID          : 0
High2key and Low2key len. : 8
Avg column length in chars: -1
Delimiter length          : -1
Number of Subelements     : -1
Number of nulls           : 0
High2key                  : 2
Low2key                   : 1
Statistic Descriptor      :
colid                    : 0
fd offset                : 16
hd offset                : 64
nmostfreq                : 2
mtiles                   : 4
Histogram Descriptor      : Count   Distcount   Value_len   Value
                             ---------------------------------------------
                             0     0     4            1
                             1280     0     4            1
                             1280     0     4            2
                             2560     0     4            2
Frequency Descriptor      : Count   Value_len   Value
                             ---------------------------------
                             1280 4             1
                             1280 4             2
Security Label ID         : 0


COLUMN NAME               : S
Length of column name     : 1
Column id                 : 1
Col type {hex, type}      : 0x0101 , VARCHAR
Col dist. type {hex, type}: 0x0038 , VARCHAR
Column length             :   2000
Blob length               : 2000
Statistic offset          : 504
Column String Units       : S
Flags                     : 0x20000090
                             - SQLRG_MIXED
                             - SQLRG_NULLIND
Flags2                    : 0x00000000
Codepage                  : 1208
Collation name            : IDENTITY
Collation ID in hex       : B'00 00 00 02 FF 00 FF FF
Length of user default    : 0
Logged                    : 0
Compact                   : 0
Inline Length             : 0
Datalink Features         : 000000
Ref. row type desc. offset: 0
No. unique values in col. : 2
Average column length     : 86
Expected Change Rate      : 0.000781
Percentage Encoded        : -1
Page Variance Ratio       : -1
Source column ID          : 0
High2key and Low2key len. : 66
Avg column length in chars: 81
Delimiter length          : -1
Number of Subelements     : -1
Number of nulls           : 0
High2key                  : {'This is a long teeeeeeeeeeeeeeee}
High2key in Hex Format    : 27546869732069732061206c6f6e67207465656565656565656565656565656565
Low2key                   : {'Another                         }
Low2key in Hex Format     : 27416e6f7468657220202020202020202020202020202020202020202020202020
Statistic Descriptor      :
colid                    : 1
fd offset                : 16
hd offset                : 96
nmostfreq                : 2
mtiles                   : 4
Histogram Descriptor      : Count   Distcount   Value_len   Value Hex(value)
                             ---------------------------------------------
                             0     0     8            {'Another}   27416e6f74686572
                             1280     0     8            {'Another}   27416e6f74686572
                             1280     0     33           {'This is a long teeeeeeeeeeeeeeee}   27546869732069732061206c6f6e67207465656565656565656565656565656565
                             2560     0     33           {'This is a long teeeeeeeeeeeeeeee}   27546869732069732061206c6f6e67207465656565656565656565656565656565
Frequency Descriptor      : Count   Value_len   Value Hex(value)
                             ---------------------------------
                             1280 8             {'Another}   27416e6f74686572
                             1280 33            {'This is a long teeeeeeeeeeeeeeee}   27546869732069732061206c6f6e67207465656565656565656565656565656565
Security Label ID         : 0

Monday, July 28, 2014

Quiz? State of Affairs...!

This is not really a quiz (if you followed by advice to try out IBM Bluemix), but merely shows the state of affairs:

Current State

What does it say to the trained eyes? Where was screenshot taken, what does it indicate? Any takers?

Tuesday, July 8, 2014

DB2 Quiz: Processes and CPU

In today's DB2 quiz the focus is on DB2 processes and CPU consumption. Which SQL statement did I run in DB2 for the following output? What function is used?

NAME                                CPU_USER             CPU_SYSTEM
----------------------------------- -------------------- --------------------
db2fmp (                                              13                    4
db2fmp (                                               9                   10
db2vend (PD Vendor Process - 1) 0                      5                    2
db2ckpwd 0                                             0                   13
db2ckpwd 0                                             0                   13
db2ckpwd 0                                             0                    9
db2sysc 0                                          18561                30282
db2wdog 0 [hloeser]                                    6                   44
db2acd 0                                            6997                11550

9 record(s) selected.

A statement similar to the one I used can be found in the DB2 Knowledge Center. The statement makes use of a special table function.

Tuesday, June 17, 2014

DB2 Screenshot Quiz: Where is this taken from?

I am using different DB2-related services, such as the new Knowledge Center for DB2, BLU for Cloud (DB2 with BLU Acceleration in the Cloud), IBM Bluemix, and of course a local DB2 installation. Where did I find the following graphic? It is part of one of the above mentioned services...

Let me know by comment or direct email.

Tuesday, May 20, 2014

SQL Quiz: Which command did I run?

I have a small DB2 test database which I wanted to clean up. I ran a command which has the output below. Which command was it?

Table/View                      Schema          Type Creation time
------------------------------- --------------- ----- --------------------------
FOO                             HLOESER         T     2014-03-19-12.14.19.598515
INUSE                           HLOESER         T     2014-05-08-11.49.18.419975

2 record(s) selected.

BTW: The command can be found in the IBM Knowledge Center which will be/is replacing and integrating the DB2 Information Center.

Friday, April 11, 2014

DB2 Quiz: Find the website for this screenshot

Today's DB2 quiz is not that technical, but it requires that you are up-to-date on IBM's offerings for DB2. What is the context for this screenshot? On which website did I take it?

Probably easy to solve for you guys. Enjoy the weekend.

Thursday, April 3, 2014

(SOLVED) DB2 Quiz: What function do I call?

Today, it is time for another DB2 quiz. I am going to present you the output of a DB2 function:

1
---
YES

1 record(s) selected.
Which built-in function did return this result? I invoked the function this way "values funcname", no parameters were involved. The function was added to DB2 in version 10.1.

Added on April 3rd:
As you can see from the comments, the quiz has been solved. I called the administrative function ADMIN_GET_INTRA_PARALLEL(). The function returns either YES or NO depending on whether the database application will run with intraparallel parallelism enabled or not. In DB2 10.1 several enhancements were made to exploiting parallelism. In addition to the database manager (dbm) switch INTRA_PARALLEL to enable or disable parallelism, it is also possible to call the new procedure ADMIN_SET_INTRA_PARALLEL() to configure parallelism for a specific connection.

Thursday, December 12, 2013

The DB2 10.5 Year End Quiz

This is the last quiz for 2013 and before I take a break ("vacation"). When looking at the English language version of the DB2 10.5 Information Center, what do these four (4) pages have in common?

Where condition in SQL warehousing transform operator:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.dwe.sqw.doc/designing/data_flow/ropwhere.html

SEND procedure - Send E-Mail to an SMTP server:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0055177.html

UTL_SMTP module:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0055180.html

The SAMPLE database:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.samptop.doc/doc/r0001094.html

Any guesses? It is about the samples and the current season...

Enjoy the holidays and all the best for 2014!

Friday, October 18, 2013

Friday DB2 Quiz: Not much to report...

It is Friday. How about a quick quiz? What did I do to produce the following output in DB2? Don't comment on the (in)activities you see... :)

Result set 1
--------------

TEXT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Monitoring report - database summary
--------------------------------------------------------------------------------
Database:                                 HL
Generated:                                10/18/2013 09:43:38
Interval monitored:                       10

================================================================================
Part 1 - System performance

Work volume and throughput
--------------------------------------------------------------------------------
                                    Per second             Total
                                    --------------------- -----------------------
TOTAL_APP_COMMITS                 0                      0
ACT_COMPLETED_TOTAL               24                     246
APP_RQSTS_COMPLETED_TOTAL         0                      3

TOTAL_CPU_TIME                    = 25439
TOTAL_CPU_TIME per request        = 8479

Row processing
    ROWS_READ/ROWS_RETURNED         = 0 (40/246)
    ROWS_MODIFIED                   = 0

Wait times
--------------------------------------------------------------------------------

-- Wait time as a percentage of elapsed time --

                                           %    Wait time/Total time
                                           --- ----------------------------------
For requests                             2    260/10659
For activities                           2    221/10512

-- Time waiting for next client request --

CLIENT_IDLE_WAIT_TIME               = 4665
CLIENT_IDLE_WAIT_TIME per second    = 466

-- Detailed breakdown of TOTAL_WAIT_TIME --

                                %    Total
                                --- ---------------------------------------------
TOTAL_WAIT_TIME               100 260

I/O wait time
    POOL_READ_TIME              0    0
    POOL_WRITE_TIME             0    0
    DIRECT_READ_TIME            80   210
    DIRECT_WRITE_TIME           0    0
    LOG_DISK_WAIT_TIME          0    0
LOCK_WAIT_TIME                0    0
AGENT_WAIT_TIME               0    0
Network and FCM
    TCPIP_SEND_WAIT_TIME        0    0
    TCPIP_RECV_WAIT_TIME        0    0
    IPC_SEND_WAIT_TIME          0    0
    IPC_RECV_WAIT_TIME          0    0
    FCM_SEND_WAIT_TIME          0    0
    FCM_RECV_WAIT_TIME          0    0
WLM_QUEUE_TIME_TOTAL          0    0
CF_WAIT_TIME                  0    0
RECLAIM_WAIT_TIME             0    0
SMP_RECLAIM_WAIT_TIME         0    0

Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --

                                      %                 Total
                                      ---------------- --------------------------
Total processing                    100               10399

Section execution
    TOTAL_SECTION_PROC_TIME           2                 220
      TOTAL_SECTION_SORT_PROC_TIME    0                 0
Compile
    TOTAL_COMPILE_PROC_TIME           1                 109
    TOTAL_IMPLICIT_COMPILE_PROC_TIME 0                 0
Transaction end processing
    TOTAL_COMMIT_PROC_TIME            0                 0
    TOTAL_ROLLBACK_PROC_TIME          0                 0
Utilities
    TOTAL_RUNSTATS_PROC_TIME          0                 0
    TOTAL_REORGS_PROC_TIME            0                 0
    TOTAL_LOAD_PROC_TIME              0                 0

Buffer pool
--------------------------------------------------------------------------------
Buffer pool hit ratios

Type             Ratio            Reads (Logical/Physical)
--------------- --------------- ----------------------------------------------
Data             100              29/0
Index            100              28/0
XDA              0                0/0
COL              0                0/0
Temp data        0                0/0
Temp index       0                0/0
Temp XDA         0                0/0
Temp COL         0                0/0
GBP Data         0                (0 - 0)/0
GBP Index        0                (0 - 0)/0
GBP XDA          0                (0 - 0)/0
GBP COL          0                (0 - 0)/0
LBP Data         100              (29 - 0)/(29 + 0)
LBP Index        100              (28 - 0)/(28 + 0)
LBP XDA          0                (0 - 0)/(0 + 0)
LBP COL          0                (0 - 0)/(0 + 0)

I/O
--------------------------------------------------------------------------------
Buffer pool writes
    POOL_DATA_WRITES      = 0
    POOL_XDA_WRITES       = 0
    POOL_INDEX_WRITES     = 0
    POOL_COL_WRITES       = 0
Direct I/O
    DIRECT_READS          = 802
    DIRECT_READ_REQS      = 32
    DIRECT_WRITES         = 0
    DIRECT_WRITE_REQS     = 0
Log I/O
    LOG_DISK_WAITS_TOTAL = 0

Locking
--------------------------------------------------------------------------------
                          Per activity                    Total
                          ------------------------------ ----------------------
LOCK_WAIT_TIME          0                               0
LOCK_WAITS              0                               0
LOCK_TIMEOUTS           0                               0
DEADLOCKS               0                               0
LOCK_ESCALS             0                               0

Routines
--------------------------------------------------------------------------------
                                Per activity              Total
                                ------------------------ ------------------------
TOTAL_ROUTINE_INVOCATIONS     0                         237
TOTAL_ROUTINE_TIME            41                        10294

TOTAL_ROUTINE_TIME per invocation   = 43

Sort
--------------------------------------------------------------------------------
TOTAL_SORTS                         = 4
SORT_OVERFLOWS                      = 0
POST_THRESHOLD_SORTS                = 0
POST_SHRTHRESHOLD_SORTS             = 0

Network
--------------------------------------------------------------------------------
Communications with remote clients
TCPIP_SEND_VOLUME per send          = 0          (0/0)
TCPIP_RECV_VOLUME per receive       = 0          (0/0)

Communications with local clients
IPC_SEND_VOLUME per send            = 94         (189/2)
IPC_RECV_VOLUME per receive         = 160        (482/3)

Fast communications manager
FCM_SEND_VOLUME per send            = 0          (0/0)
FCM_RECV_VOLUME per receive         = 0          (0/0)

Other
--------------------------------------------------------------------------------
Compilation
    TOTAL_COMPILATIONS                = 2
    PKG_CACHE_INSERTS                 = 16
    PKG_CACHE_LOOKUPS                 = 17
Catalog cache
    CAT_CACHE_INSERTS                 = 3
    CAT_CACHE_LOOKUPS                 = 9
Transaction processing
    TOTAL_APP_COMMITS                 = 0
    INT_COMMITS                       = 0
    TOTAL_APP_ROLLBACKS               = 0
    INT_ROLLBACKS                     = 0
Log buffer
    NUM_LOG_BUFFER_FULL               = 0
Activities aborted/rejected
    ACT_ABORTED_TOTAL                 = 0
    ACT_REJECTED_TOTAL                = 0
Workload management controls
    WLM_QUEUE_ASSIGNMENTS_TOTAL       = 0
    WLM_QUEUE_TIME_TOTAL              = 0

DB2 utility operations
--------------------------------------------------------------------------------
    TOTAL_RUNSTATS                    = 0
    TOTAL_REORGS                      = 0
    TOTAL_LOADS                       = 0

================================================================================
Part 2 - Application performance drill down

Application performance database-wide
--------------------------------------------------------------------------------
TOTAL_CPU_TIME          TOTAL_       TOTAL_APP_     ROWS_READ +
per request             WAIT_TIME % COMMITS        ROWS_MODIFIED
---------------------- ----------- ------------- ----------------------------
8479                    2            0              40

Application performance by connection
--------------------------------------------------------------------------------
APPLICATION_   TOTAL_CPU_TIME        TOTAL_         TOTAL_APP_     ROWS_READ +
HANDLE         per request           WAIT_TIME %    COMMITS        ROWS_MODIFIED
------------- -------------------   -----------    ------------- -------------
804            8479                  2              0              40

Application performance by service class
--------------------------------------------------------------------------------
SERVICE_       TOTAL_CPU_TIME        TOTAL_         TOTAL_APP_     ROWS_READ +
CLASS_ID       per request           WAIT_TIME %    COMMITS        ROWS_MODIFIED
--------       -------------------   -----------    ------------- -------------
4              0                     0              0              0
11             0                     0              0              0
12             0                     0              0              0
13             8479                  2              0              40

Application performance by workload
--------------------------------------------------------------------------------
WORKLOAD_      TOTAL_CPU_TIME          TOTAL_       TOTAL_APP_     ROWS_READ +
NAME           per request             WAIT_TIME % COMMITS        ROWS_MODIFIED
------------- ---------------------- ----------- ------------- -------------
SYSDEFAULTADM 0                       0            0              0
SYSDEFAULTUSE 8479                    2            0              40

================================================================================
Part 3 - Member level information

- I/O wait time is
    (POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME).

          TOTAL_CPU_TIME          TOTAL_       RQSTS_COMPLETED_ I/O
MEMBER per request             WAIT_TIME % TOTAL             wait time
------ ---------------------- ----------- ---------------- -----------------
0       8479                    2            3                 261


241 record(s) selected.

Return Status = 0

Data Henrik

Pages