About the document

  • This is a getting started guide to start IDT-Manager on EC2 instance.

    • Start IDT-Manager on EC2 instance from AMI.

    • Configure PISO-TGT setting to point to the launched IDT-Manager.

    • Evaluate SQLs on Target DB.

  • Follow the Insight Database Testing user guide for further details.

Prepare IDT-Manager

Start IDT-Manager on EC2 instance

  • Start EC2 instance with following setting.

    • AMI: (select IDT-Manager AMI at AWS Marketplace)

    • Instance type: over 4 core vCPU and over 8GB RAM. (m5.xlarge is recommended)

    • Storage: 50GB for a monitored instance generally. (See Installation Guide for more details)

      • /dev/sdb: online data / Insight Database Testing working area

      • /dev/sdc: backup data

    • Security group: ssh, http(7777)

      • PISO-TGT must be able to connect to port 7777 of IDT-Manager.

      • Port 7777 is used for Web-UI.

  • Logon to the EC2 instance with 'centos' user.

Setup PISO-Manager

  • Set hostname. For '<HOSTNAME>', you can give it the name you want.

sudo hostnamectl set-hostname --static <HOSTNAME>

  • Recognize expanded volume.
    If you have expanded volume size, you must use file system–specific commands to extend the file system to the larger size.

sudo xfs_growfs -d /mnt/piso-data
sudo xfs_growfs -d /mnt/piso-backup

Web-UI access

  • You can access to PISO-Manager web UI. The URL is 'http://<IP_ADDRESS>:7777/piso/'.
    The default username is 'administrator', and the default password is your EC2 instance id.

  • You can access to IDT-Manager web UI. The URL is 'http://<IP_ADDRESS>:7777/idt/'.
    The default password is your EC2 instance id.

Configure PISO-TGT setting to point to the launched PISO-Manager.

Attention

  • Assuming that PISO-TGT has already been installed on your DB server.

  • Follow PISO-TGT installtion guide for more details to install and configure PISO-TGT on your DB.

Configure PISO-TGT

  • Stop PISO-TGT process

istctl downistcmon
istctl stopall

  • Setup PISO-TGT to connect to PISO-Manager. Be sure that PISO-Manager’s securiy group is set up for connectted from PISO-TGT. (tcp/7777).

istctl setconfig

  • Set PISO-Manager IP address

    • Select 'STEP 1 Setup ISM'

      • Set IP Address of PISO-Manager.

      • Set 'C' for LANG.

      • Leave other information.

    • Select 'STEP 4 Register ISM'

  • Start PISO-TGT.

istctl upistcmon
istctl startall

  • Check PISO-TGT service status. (All processes must be runnging.)

istctl status

Configure PISO SQL collection

  • Confiugre PISO SQL collection and alert setting at PISO-Manager Web-UI.

    • Follow PISO-Manager setting guide for details.

  • After the configuration, database activities on the target database will be monitored.

Output collected SQLs to a CSV file.

  • Select [Search]-[Mining Search].

  • Output collected SQLs to CSV file with following setting.

    • Otuput type: CSV

    • Output directory: /mnt/piso-data/idt-data/src

    • keep [SQL] tab selected

piso manager ms setting

  • Select [Submit].

  • Confirm the job is submitted and finished on Runnning Job and Completed Jobs Tab.

piso manager ms completed

SQL Evaluation (on Insight Database Testing Web UI)

Attention

  • Following steps show very basic instructions to evaluate collectted SQLs.

    • These setps are operated on 'http://<IP_ADDRESS>:7777/idt/'.

  • Follow the Insight Database Testing user guide for further details.

  • Only Japanese menu strings are available at idt Web-UI at current version.

Create Test Data Set

Prepare '評価SQLセット'(SQLs for assessment) for SQL evaluation.

  • Select '評価SQLセット' menu.

  • Select '新規作成'(new).

idt test data set list

  • Set following info.

    • '評価SQLセット名'(name): name of '評価SQLセット'

    • 'データ元ファイル'(csv file): select output csv file from /src.

    • 'ソース DB'(source DB setting name): set source database info at following window.

    • 'ユニークSQL'(make SQLs unique): Enabled(green) or Disabled

idt create test data set
idt src db

  • Select [新規作成].

Set Target Database

  • Select 'ターゲットDB'(target database) menu.

  • Select '新規作成'(new).

  • Set following target database info.

    • 'ターゲットDB名'(Name): name of 'ターゲットDB'

    • 'データベース'(Type of database): target database type

    • 'バージョン'(Version): (database version string)

    • 'ホスト名'(Host Name): IP address of target database (Use IP address of PISO-TGT for the test.)

    • 'ポート'(Port): port No. of the database server

    • 'データベース名'(DB Name): database name

idt create target database

  • Select [新規作成].

  • After adding test database, select the database and select [テスト接続](Test Connection).

  • Set 'ユーザー'(user) and 'パスワード'(password) for the target database and select [テスト接続] to check the connection.

idt target database test connection

  • Select created Target Database from the list. (Click the name.)

Execute on Target DB

  • Select 'アセスメント'(assessment) menu.

  • Select '新規作成'(new).

  • Set following info.

    • 'アセスメント名'(Assessment name): neme of the assessment result.

    • '評価SQLセット名'(SQLs for assessment name): name of '評価SQLセット'

    • 'ターゲットDB名'(Target database name): name of 'ターゲットDB'

    • '実行タイプ'(Execute Type): '実行'(Execute) or 'パース'(Parse)

    • 'トランザクション'(Transaction): 'コミット'(Commit), 'ロールバック'(Rollback), 指定なし(depends on db setting)

    • '0秒の仮定値': (the value when the execution time is almost zero.)0.2

idt create assessment

  • Select '新規作成'(new).

  • Input the 'パスワード'(password) for database user.

  • Select [アセスメント(assessment)].

Confirm the result.

  • Select [アセスメント] from the menu.

idt assessment

  • Select 'アセスメント名'(name) of the result.

idt assessment 1

  • Select [実行できたすべてのSQLを見る] at right bottom. (Show all SQLs which are executed successfully.)

  • Select a SQL entry. Following image shows the execution result.

idt assessment 1 sql