Database

4 followers

0 questions

6 posts

When installing Semarchy on a PostgreSQL database, customers sometimes run into errors like this:

+- ERROR: function uuid_generate_v1() does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

This error can occur when the search path in the database is not correctly configured.

Do you have a search_path problem?

The above error will be thrown if:

• The function does not exist.
• The function exists, but you do not have access to it.
• The function exists, and you have access, but it is not in the search_path.

/* First confirm... (More)

This article shows how to create a new Data Location. A Data Location is the physical instance of your model and will contain all data you want to store in your Data Model. You need to set up different Data Location for different usages/project phases: Design, Initial Data Load Tests, Integration Tests, UAT and Validation, Pre-Production, and Production.

Create a new Data Location

• Connect to the Application Builder using your user name and password or your single sign-on profile.
• In the Application Builder home page, select Management

• In Management panel (left), right click on Data Location, and... (More)

When investigating performance, an Explain Plan is a useful tool to understand what steps are costing the most and therefore impacting performance the most. This article describes how to obtain an Explain Plan with Oracle. For PostgreSQL, you can consult the dedicated article here.

Syntax for generating the Explain Plan

explain plan for
/* provide the body of the select/update/insert/merge statement here*/
;

If executed successfully, you will see the output "plan FOR succeeded."

Syntax for retrieving the Explain Plan

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

This command retrieves the last explain plan. You can uniquely identify an explain plan using... (More)

Context

During development, it's common to want to remove all data from a data location in order to reload it. Use cases are:

• Matching or Consolidation rules have changed, and you want to apply those changes.
• Hard-deleting all the data from the data location.

Principle

The procedure consists of generating 'truncate' SQL statements, which you will then run on your schema. The generation of these statements relies on metadata tables and differs depending on the database you are using.

Generate truncate instructions for Oracle

select 'truncate table MY_SCHEMA.' || table_name || ';' as generated_statements
from all_tables 
where
owner = 'MY_SCHEMA'... (More)