Created by James Hurrell, last modified by Shared Doc User on Apr 22, 2021
This documentation is not maintained. Please refer to doc.castsoftware.com/technologies to find the latest updates.


SQL Reports

How to run an SQL report

Steps to run a SQL report

Step 1: Open pgAdmin

Step 2: Click on Execute arbitrary SQL queries, connected to your PosgreSQL instance:

Step 3: Copy your script in SQL Editor

Step 4: Click Replace All, to make the replacements.

Step 5: Change the output of your script : menu Query, option Execute to file, change the file name, than click OK. You will receive the confirmation of your export: Data export completed successfully. 

Step 6: Open your file and check the result

SQL Reports

List of the columns

The attached SQL file list_of_the_columns.sql could be used to generate the list of the columns, here is the description:

    • Database name, if one
    • Schema name
    • Table name
    • Column name
    • Column type

List of the transactions and columns / tables accessed by transactions

The attached SQL file list_of_transactions_columns.sql could be used to generate the list of the columns / tables accessed by transactions, here is the description:

    • Transaction Name
    • Technical name of the transaction, full name

    • Database name, if one

    • Schema name

    • Table name

    • Column name

    • Kind of the access: READ / WRITE

Analysis Unit Reports

Hierarchy of the objects 

There are two unit reports:

  • For the first one the input is a folder, e.g. deployment folder,
  • For the second the input is a knowledge base. The format of the output is JSON.

Command line when the input is a folder:
fullnames_report.read_path_symbols('folder_name')
e.g.:
fullnames_report.read_path_symbols('../../tests/diags')

Examples : Generated_6f62ed57-ceee-4610-a703-ccb2c9802fdc_PreReport.json, Generated_3c59dd03-c6ff-4842-8602-dca85dd298f2_PreReport.json

Command line when the input is a knowledge base:

server = Server()
kb = server.get_schema('local_name')
application = kb.get_application('application_name')
kb_fullnames_report.KBFullNamesReport.start_application(self,
application)
e.g.:
server = Server()
kb = server.get_schema('sqlreports_local')
application = kb.get_application('castpubs')
kb_fullnames_report.KBFullNamesReport.start_application(self,
application)

Examples : Generated_eaa659ae-79fc-40b1-bf9a-97a4d30b3e0f_KBReport.json, Generated_3a638d4f-482f-41e5-91a3-9862b84453ae_KBReport.json

List of the objects having the same name in different schemas

Command line:
fullnames_report.read_path_symbols('folder_name',2)
e.g.:
fullnames_report.read_path_symbols('duplicated_objects',2)

The output is text format and it looks like:

ObjectFullName;File
TOTO.utilisateur;duplicated_objects\test1.sql
TITI.utilisateur;duplicated_objects\test1.sql

List of the columns with sensitivity for data sensitivity

The report expected to have a parametrization file, e.g.: sensitivity_columns_param.txt.

Command line:
columns_report.read_columns_from_path('sql_folder_name', '.parametrization_file_name')
e.g.:
columns_report.read_columns_from_path('../../tests/diags', '..\sensitivity_columns_param.txt')
columns_report.read_columns_from_path('../../tests/mssql/castpubs', '..\sensitivity_columns_param.txt')

Example of report : Generated_Sensitivity_c1d8c183-539f-4e25-8a36-0ea818d5632c.datasensitive