IBM Support

A script to create the missing tables DATAMART (with example for Synthetic Agent in APM 8.1.4)

Technical Blog Post


Abstract

A script to create the missing tables DATAMART (with example for Synthetic Agent in APM 8.1.4)

Body

Problem:

We are using a remote DB2 with IBM Cloud APM v8.1.4. We do not see any error in the UI but we are not saving any data in the DB2. Data in the UI for synthetic monitoring are only coming from MongoDB.

 

Description:

During APM install time there was an error "CREATE UNIQUE INDEX" which prevented all these tables to be created

---run-------

CREATE UNIQUE INDEX D_USER_AGENT_STRING_UNIQUE_IDX ON D_USER_AGENT_STRING (NAME ASC) MINPCTUSED 0 ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COMPRESS YES

--error------

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:

SQL0614N The index or index extension "D_USER_AGENT_STRING_UNIQUE_IDX" cannot be created or altered because the combined length of the specified columns is too long. SQLSTATE=54008

---------------

 

We are getting the following message in the message.log for min service: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=ITMUSER.D_SYNTHETIC_TRANSACTION_TYPE, DRIVER=4.22.29

------------

It seems to only effect SQLERRMC=ITMUSER.F_SYNTHETIC_TRANSACTION

 

Is there a script that the customer can run to create the missing tables?

 

Run "datamart_ddl.sql.updated" script as first suggested solution,

 

but the output says:

(RESOURCE_ID) DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token ")" was found following "(RESOURCE_ID". Expected tokens may include: "<join_type_without_spec> JOIN <join_operand>". SQLSTATE=42601 REFERENCES D_RESOURCE DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "EFERENCES D_RESOURCE". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

 

Check results of db2 connect to datamart and db2 describe table DB2APM2.D_USER_AGENT_STRING

 

Login to the DB2 server with ITMUSER and perform these steps:

(since only ITMUSER was granted CONNECT and DBADM permissions)

1) db2 connect to DATAMART user ITMUSER using itmuser-password

2) db2 "list tables" > tables.out

3) Provide the tables.out file

4) db2 describe table ITMUSER.D_USER_AGENT_STRING

5) Provide the output of the db2 describe table command

 

Output in create_missing_tables.log show that tables seem to be there but there are no data in D_SYNTHETIC_TRANSACTIONS.

 

Explanation:

When there are missing tables in the DATAMART DB then Synthetic transactions will not be saved to the DATAMART DB but will continue to be written to the MongoDB database.

 

The APM UI only shows synthetics data from MongoDB. The Synthetics data in the DATAMART DB is only used by Synthetic reports if you have installed and configured the IBM Tivoli Common Reporting product and integrated it with APM.

 

SQL0614N The index or index extension "D_USER_AGENT_STRING_UNIQUE_IDX" cannot be created or altered because the combined length of the specified columns is too long.

SQLSTATE=54008 means a column of the key is too long, or the key has too many columns or periods.

Thus index or index-name cannot be created or altered because the combined length of the specified columns is too long.

 

USERSPAC1 tablespace show a Page size of 4K but it should have Page size of 8K. The APM Datamart creation script (create_datamart_db.sql) creates the database with a default page size of 8K but it has Page size of 4K. This is the reason that the Datamart DDL script failed when it was run during the APM server install.

 

In this case may be the best to drop the DATAMART database and recreate it with the expected tablespace sizes (since APM server logs show there is no data in the database).

 

 

Solution - Instructions to drop and create new DATAMART:

 

To recreate the DATAMART database, you will need 2 files: create_datamart_db.sql and update_datamart_performance.sql which come with the APM installation image (or please contact IBM Support to get this files). Copy create_datamart_db.sql file to the DB2 server and ensure your DB2 instance user has read/write access to the file. Copy update_datamart_performance.sql file to the APM server and ensure that the db2apm user has read/write access to it. Then perform following steps to drop DATAMART and to create new DATAMART.

 

A) On your DB2 server:

-------------------

1) Login as your DB2 instance user

2) Drop the existing DATAMART DB:

db2 drop db DATAMART

Note: if the database cannot be dropped, you will need to stop the services on the APM server (apm stop_all) and then drop the database since some of the processes may have open connections to the database.

3) Change to the directory where you copied the create_datamart_db_sql file and run this command:

db2 -vf ./create_datamart_db.sql

and review the command output to verify all commands complete successfully

-------------------

 

B) On the APM server, perform these steps as root:

-------------------

1) Create /tmp/create-datamart directory

2) chmod 777 /tmp/create-datamart

3) chmod -R o+w /opt/ibm/dtl/datalayer/install/scripts/schema

4) chmod o+w /opt/ibm/dtl/datalayer/cron/datamart.cfg

5) su - db2apm

6) Source db2apm profile:

. /home/db2apm/sqllib/db2profile

Note: there is a period and a space before /home

7) db2 connect to DATAMART user itmuser using db2Usrpasswd@08

Note: Replace db2Usrpasswd@08 with your password for itmuser

8) cd /opt/ibm/dtl/datalayer/install/scripts

9) ./create_datamart.sh -db DATAMART -user itmuser -pwd db2Usrpasswd@08 -logpath /tmp/create-datamart -startOfWeek 1

Note: Replace db2Usrpasswd@08 with your password for itmuser

10) db2 list tables

Verify the command displays this at the end:

57 record(s) selected.

11) ./datamart_post_install.sh -db DATAMART -user itmuser -pwd db2Usrpasswd@08 -etluser itmuser -logpath /tmp/create-datamart

Note: Replace db2Usrpasswd@08 with your password for itmuser

12) Review /tmp/create-datamart/datamart_post_install.log file to confirm everything completed successfully

13) db2 connect to DATAMART user db2apm using db2Usrpasswd@08; db2 -svf /path-to-downloaded-file/update_datamart_performance.sql

Notes:

- Replace db2Usrpasswd@08 with your Db2 instance user password on the Db2 server. If you are using an instance user other than db2apm on the Db2 server, specify your Db2 instance user name in place of db2apm.

- Replace path-to-downloaded-file with the directory where you downloaded the update_datamart_performance.sql file

13) db2 connect to DATAMART user itmuser using db2Usrpasswd@08; db2 bind /opt/ibm/db2/V10.5/bnd/db2clipk.bnd collection NULLIDRA

Note: Replace db2Usrpasswd@08 with your password for itmuser

Verify the output looks like this:

LINE MESSAGES FOR db2clipk.bnd

------

SQL0061W The binder is in progress.

SQL0091N Binding was ended with "0" errors and "0" warnings.

 

14) Log back in as root

15) chmod -R o-w /opt/ibm/dtl/datalayer/install/scripts/schema

16) chmod 600 /opt/ibm/dtl/datalayer/cron/datamart.cfg

17) cd /opt/ibm/synthetic_cron

18) chmod 666 synthetic_cron.sql

19) ./enable-synthetic-cron.sh

20) chmod 644 synthetic_cron.sql

-------------------

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSVJUL","label":"IBM Application Performance Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB15","label":"Integration"}}]

UID

ibm11277440