Here is a quiz for all those administrators and DB2 fans that have insights into more than the core engine (that is the hint...:).
What are these two tables used for?
hloeser@rotach:~$ db2 "describe table MANAGED_DATABASE"
Data type Column
Column name schema Data type name Length Scale Nulls
-------------------------- --------- ------------------- ---------- ----- ------
NAME SYSIBM VARCHAR 255 0 No
1 record(s) selected.
hloeser@rotach:~$ db2 "describe table MANAGED_DATABASE_PROPS"
Data type Column
Column name schema Data type name Length Scale Nulls
-------------------------- --------- ------------------- ---------- ----- ------
NAME SYSIBM VARCHAR 255 0 No
PROPERTY_KEY SYSIBM VARCHAR 255 0 No
PROPERTY_VALUE SYSIBM VARCHAR 255 0 Yes
3 record(s) selected.
Any guesses or even some background information you want to share?
(Updated) Hint 2: It is related to Data Studio.
Showing posts with label data studio. Show all posts
Showing posts with label data studio. Show all posts
Tuesday, September 17, 2013
Wednesday, August 7, 2013
IOQWT - A typical IBM acronym that works for DB2 BLU
IOQWT is a typical acronym, used to deal with lengthy product names like IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows. It is part of the InfoSphere Optim suite of tools for data lifecycle management. IOQWT usually helps to tune single queries or entire workloads. Some licenses for the tool are included in Advanced Editions and the Developer Edition of DB2. That is how I got my fingers on it. And the reason I had to try out using IOQWT is that the so-called Workload Table Organization Advisor (WTOA...?) can predict whether it makes sense to convert tables from row organization to a column-organized layout, i.e., whether DB2 with BLU Acceleration is a big benefit.
My journey started by starting up Data Studio with IOQWT integration. In the so-called Data Source Explorer I had to activate my database for tuning. After the license had been applied and additional internal-use tables been created, I could start tuning, i.e., invoke the tuning advisors. The first was to tell IOQWT what statements to analyze. It allows to directly paste a single query as text, however, it only recommends table organizations for a workload, a set of queries. Thus I chose to import a file with three queries. My test database uses a TPC-H schema and I imported 3 queries.
After saving the 3 queries to a workload, IOQWT was ready for action. As next step I could select what types of advisors it should run and what type of tuning suggestions I was interested in. After marking Table Organization as the kind of advice I proceeded to the next step, waiting for results. :)
As you can see below, IOQWT analyzed six tables from my small TPC-H database. If I would convert tables to column organization (BLU Acceleration), it predicted a performance improvement of 98.79%. The most gain would be for the first query which right now has the most costs and would have least costs associated after the conversion. So DB2 with BLU Acceleration seems to make sense for my workload and by analyzing it in IOQWT I got that information without converting my database.
That's it for today, see older posts on DB2 with BLU Acceleration.
My journey started by starting up Data Studio with IOQWT integration. In the so-called Data Source Explorer I had to activate my database for tuning. After the license had been applied and additional internal-use tables been created, I could start tuning, i.e., invoke the tuning advisors. The first was to tell IOQWT what statements to analyze. It allows to directly paste a single query as text, however, it only recommends table organizations for a workload, a set of queries. Thus I chose to import a file with three queries. My test database uses a TPC-H schema and I imported 3 queries.
After saving the 3 queries to a workload, IOQWT was ready for action. As next step I could select what types of advisors it should run and what type of tuning suggestions I was interested in. After marking Table Organization as the kind of advice I proceeded to the next step, waiting for results. :)
As you can see below, IOQWT analyzed six tables from my small TPC-H database. If I would convert tables to column organization (BLU Acceleration), it predicted a performance improvement of 98.79%. The most gain would be for the first query which right now has the most costs and would have least costs associated after the conversion. So DB2 with BLU Acceleration seems to make sense for my workload and by analyzing it in IOQWT I got that information without converting my database.
That's it for today, see older posts on DB2 with BLU Acceleration.
 |
| Results from Optim Query Workload Tuner suggesting DB2 BLU |
Friday, November 16, 2012
Where to find information about granted privileges in DB2
Yesterday, I reveived a question about where to find specific metadata in DB2. The customer wanted to find out which privileges had been granted within a database and they were aware that db2look can produce this list. But where does this information come from?
Let's start with a glimpse at db2look. It is the "DB2 statistics and DDL extraction tool" and can be used to produce the DDL statements for the objects inside a database. There are also options specific to handling authorizations, i.e., the privileges (see the -x, -xdep, -xd, and -xddep options). All the statements that db2look produces are based on special data stored in the database, the so-called metadata. Those tables that hold the metadata are called System Catalog in DB2 and Data Dictionary in Oracle. The system catalog than can be queried using regular SELECT statements because the information is provided in tables (users can stay within the data mode, the relation model).
DB2 offers the metadata in two different sets of views. The views in the SYSCAT schema basically have all the metadata. The views in the SYSSTAT schema have a subset of the data and have (some) updatable columns, so that object statistics can be changed (for good or for worse...). The views are built on top of the internal catalog tables which are managed in the SYSIBM schema. It is advised to only use the SYSCAT and SYSSTAT views because structure of the internal tables can change without warning whereas the external catalog views are kept intact.
Now, where can the information about granted privileges be found? The DB2 Information Center has a so-called "Road map to catalog views" which is a listing of all the offered metadata found in the SYSCAT and SYSSTAT views. All the views that end in *AUTH carry authorization information, i.e., data about privileges. To give some examples, in SYSCAT.INDEXAUTH you can find out who has CONTROL privilege on an index, SYSCAT.ROLEAUTH is useful to see who has ADMIN authority for granting a specific role, and finally, as a complex example, SYSCAT.TABLEAUTH manages all the table privileges like insert, update, delete, select, alter, control, and even more privileges.
Administration tools for DB2 access that information, procedures and scripts can select data from these views, and of course applications and users. Which of these views are made available for the PUBLIC is up to the administrators. Remember, it's an honor, not a privilege...
Comments or questions?
Let's start with a glimpse at db2look. It is the "DB2 statistics and DDL extraction tool" and can be used to produce the DDL statements for the objects inside a database. There are also options specific to handling authorizations, i.e., the privileges (see the -x, -xdep, -xd, and -xddep options). All the statements that db2look produces are based on special data stored in the database, the so-called metadata. Those tables that hold the metadata are called System Catalog in DB2 and Data Dictionary in Oracle. The system catalog than can be queried using regular SELECT statements because the information is provided in tables (users can stay within the data mode, the relation model).
DB2 offers the metadata in two different sets of views. The views in the SYSCAT schema basically have all the metadata. The views in the SYSSTAT schema have a subset of the data and have (some) updatable columns, so that object statistics can be changed (for good or for worse...). The views are built on top of the internal catalog tables which are managed in the SYSIBM schema. It is advised to only use the SYSCAT and SYSSTAT views because structure of the internal tables can change without warning whereas the external catalog views are kept intact.
Now, where can the information about granted privileges be found? The DB2 Information Center has a so-called "Road map to catalog views" which is a listing of all the offered metadata found in the SYSCAT and SYSSTAT views. All the views that end in *AUTH carry authorization information, i.e., data about privileges. To give some examples, in SYSCAT.INDEXAUTH you can find out who has CONTROL privilege on an index, SYSCAT.ROLEAUTH is useful to see who has ADMIN authority for granting a specific role, and finally, as a complex example, SYSCAT.TABLEAUTH manages all the table privileges like insert, update, delete, select, alter, control, and even more privileges.
Administration tools for DB2 access that information, procedures and scripts can select data from these views, and of course applications and users. Which of these views are made available for the PUBLIC is up to the administrators. Remember, it's an honor, not a privilege...
Comments or questions?
Tuesday, November 22, 2011
All the news: DB2 Express-C with PL/SQL, new TPoX version, DS 3.1, and hello to all PMs
I am in between business trips and there is not much time for looking deeper into any specific problems. But I wanted to touch base on few things that are new:
- DB2 Express-C, the free to download, free to use edition of DB2 LUW now includes the Oracle compatibility. That is, you can develop and use PL/SQL packages with DB2, for free, even in production. Although it still says "9.7.4" at the DB2 Express-C download site, when you click through it then offers DB2 9.7.5 for download. I just tried it.
- A new version of TPoX, the open source XML database benchmark, has been released. Most changes are to the workload driver. I know that some of you use the workload driver not just for TPoX and DB2.
BTW: The DB2 Technology Explorer/Management Console includes a so-called Workload Multiuser Driver (WMD) that can be handy, too. - IBM Data Studio 3.1 is out since few weeks and Data Studio will replace the DB2 Control Center in the near future. There is a so-called Administration Client (which is small) and a Full Client. Both have a different download size and a different function set. An overview of what is included and which database servers in addition to DB2 are supported is listed at this Data Studio V3.1 features document.
Wednesday, November 9, 2011
Ready for an IBM puzzle? Some links and a question...
Hey, are you ready for an interesting puzzle? In the following I will post some links to support articles and the Information Center and you tell me what I was up to...
This may not sound like fun, but all of us will learn and also improve patience. :)
Okay, let's get started:
This may not sound like fun, but all of us will learn and also improve patience. :)
Okay, let's get started:
- http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.wn.doc/doc/i0054250.html
- http://www-01.ibm.com/support/docview.wss?uid=swg27021898
- http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.db.web.health.install.doc/topics/dshm_roadmap.html
- http://www-01.ibm.com/support/docview.wss?uid=swg27021949
- http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.db.web.jobmanager.doc/topics/job_create_and_manage.html
- http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.adm.doc/topics/chxucofflinescripts.html
Wednesday, October 26, 2011
IBM Data Studio Version 3.1 consolidates some Optim tools
Now that the new version of IBM Data Studio is available for download, it is worth taking a look at it - even if you didn't like Data Studio and the other Optim tools so far. IBM Data Studio is free to download and to use. And it combines features from the previous Data Studio offering, from Data Studio Health Monitor, and from the Optim Database Administrator and Optim Development Studio into a single products. Given that the DB2 Control Center is deprecated as of DB2 9.7, you can take your guesses why Data Studio has been beefed up.
So what does the new Data Studio offer? It has all the base database administration capabilities, all the "wizards" for accomplishing - step by step - administration tasks. It supports development of SQL statements and procedures, including the tuning, e.g., through visual explain. And Data Studio has lots of other features which may suit to your needs. As I stated above, give it a try and let IBM know through the usual channels what you think of it...
So what does the new Data Studio offer? It has all the base database administration capabilities, all the "wizards" for accomplishing - step by step - administration tasks. It supports development of SQL statements and procedures, including the tuning, e.g., through visual explain. And Data Studio has lots of other features which may suit to your needs. As I stated above, give it a try and let IBM know through the usual channels what you think of it...
Subscribe to:
Posts (Atom)