Autor: Tom Lieber (Page 1 of 3)

Oracle Apex 23.1 Template Component

Bug or Feature ?

Should work in Cards Region.. but..

Info from Oracle:


Currently,  the Template Components cannot be used inside a Cards region. The reason is that Template Components are rendered on the server and Cards on the client.

We hope to lift this restriction in a future release, we’d like to push the Template Components to the client when needed. We must make sure the functionality is equal, currently the templates behave slightly different.

You can use the {with/} and {apply/} syntax in cards but this is undocumented. You first need to call apex.util.defineTemplates:

apex.util.defineTemplates([{

    name: „MY_TEMPLATE „,

    template: „#VAL#“

}]);

apex.util.applyTemplate(`{with/}

    VAL:=Hello World

{apply MY_TEMPLATE /}`);


Nice to know now.. i will try this solution..

Create Standby with Oracle 19.16?

I hit Bug 34446152 – [DATAGUARD_BRKR] 19.16 onward broker shows „ORA-16705: internal error in Data guard broker“ (Doc ID 34446152.8)

If you decide to create an DG Environment, do it with 19.14 or 19.20 but not with 19.15-19.19. I have not alot of infos about this Bug. But it is a … maybe you will HIT-Bug.

Okay after Upgrade (19.20)

Following Guide helps to startup DG-Standby from previouse cloned VM:

FYI: This Template VM is preconfigured to be ready for Standby Cloning with this steps:

tomdg01:

mkdir /app_oracle/fra/C10P_A/standbylog

sql+ /app_oracle/local/dbsetup/19/crdb/11_standby.sql

vi /etc/oratab => don't start the DB after cloning vm
change listener.ora -- check standby entries
change tnsnames.ora -- check standby entries

/etc/hosts -- add the tomdg02 host + ip

now we can clone


Okay now you can start und Setup the Standbyside:

tomdg02:

change listener.ora -- remove the primary and active the standby part
change tnsnames.ora -- same here
mkdir  /app_oracle/fra/C10P_B
cp /tmp/demo_stby.ctl /app_oracle/data/C10P_A/ctrl1.ctl
cp /tmp/demo_stby.ctl /app_oracle/fra/C10P_A/ctrl2.ctl
mkdir  /app_oracle/fra/C10P_B


sqlplus "/as sysdba"

startup nomount;
alter system set db_recovery_file_dest='/app_oracle/fra/C10P_B' scope=spfile;
alter system set db_unique_name=C10P_B scope=spfile;
shutdown immediate;
startup mount;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


exit;

lsnrctl start C10P

Now we can start the Primary instance tomdg01

tomdg01:

sqlplus "/as sysdba"
startup;
exit;

lsnrctl start C10P

--test the connection best on both sides!

sqlplus sys/@dg_c10p_a;
sqlplus sys/@dg_c10p_b;

Now lets enable the DG:

dgmgrl sys/@dg_c10p_a

create configuration C10P as primary database is c10p_a connect identifier is DG_C10P_A;
add database c10p_b as connect identifier is DG_C10P_B maintained as physical;

edit database c10p_b set property ApplyLagThreshold=0;
edit database c10p_b set property TransportLagThreshold=0;
edit database c10p_a set property ApplyLagThreshold=0;
edit database c10p_a set property TransportLagThreshold=0;
edit database c10p_a set property 'logxptmode' ='sync' ;
edit database c10p_b set property 'logxptmode' ='sync' ;

enable configuration ;

-- alter system switch logfile --
show configuration;
show database c10p_b;
show database c10p_a;

The Broker shows the Current status of the Standby

After a while.. 

DGMGRL> show configuration

Configuration - c10p

  Protection Mode: MaxPerformance
  Members:
  c10p_a - Primary database
    c10p_b - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 54 seconds ago)

Clone VM in ESXi

  1. Make your Template VM
  2. Copy on Filesystem
Thats the way to-go with ESXi
Copy it

Enter a new directory name .. and wait until its ready..
As you can see, my template name was not the best idea tomdg01 😉


Register the VM

Now Change the Name in ESXi

here from tomdg01 VM-Name to tomdg02

Now you can Start the VM – ESXi will ask you – is this a copy- please say yes here.

With your favourit tool to connect , start ssh too your Template VM IP
Now change the IP+Hostname:

vi /etc/hostname – rename host
vi /etc/hosts – change ip
vi /etc/sysconfig/network-scripts/ifcfg-* – change ip

Restart and be Happy

Opensearch and Exasol

Features

OpenSearch includes the following features not offered by free Elasticsearch:

We tested it in combination with Exasol!

We transfered Data from Open Search into Exasol and vice-versa.

We have learned alot.

Hit a OpenSearch BUG https://github.com/opensearch-project/sql-jdbc/issues/66

GEOLOCATED DATATYPE BUGS
Fast&Cheap

Are you interessted?

ASK TOM LIEBER

Easy Autoupgrade/Autopatch Oracle Out-of-Place 19.20 within Sphinx Environment

If not already exists a „not used“ Oracle Home. create a new one..

dbsw_install dbs 19.EE 19.20
(19.EE is our 19.16 Prepared Image) 

Now it will create our new Home in 
/app_oracle/product/dbs/19.20

(it will prompt that you have to make an root commando, please do that)

Replace the OPatch utility with the newest Version

mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch_old
unzip /app_oracle/local/tools/p6880880_210000_Linux-x86-64.zip -d $ORACLE_HOME/

Download the Patches from Oracle with WGET

Find Patchnr here:
https://support.oracle.com/knowledge/Oracle%20Database%20Products/888_1.html#19

Patch 35320081: DATABASE RELEASE UPDATE 19.20.0.0.0
Patch 35370174: COMBO OF OJVM RU COMPONENT 19.20.0.0.230718 + DB RU 19.20.0.0.230718
— Patch 35261302: DATAPUMP BUNDLE PATCH 19.19.0.0.0

ORACLE

Thx WGET its easy , i dont like the support.oracle.com Page to download Patchs..

The 19.20 DB+OJW Bundle Patch

wget --http-user=myuser@sphinx.at --http-password=Dontellu --no-check-certificate --output-document=p35370174_190000_Linux-x86-64.zip "https://updates.oracle.com/Orion/Download/download_patch/p35370174_190000_Linux-x86-64.zip"

The 19.19 DBRU Patch (which cannot be applied at the moment)
i have to wait for 19.20

wget --http-user=myuser@sphinx.at --http-password=Dontellu --no-check-certificate --output-document=p35261302_1919000DBRU_Generic.zip "https://updates.oracle.com/Orion/Download/download_patch/p35261302_1919000DBRU_Generic.zip"

Unpack the *.zip into to /app_oracle/product/dbs/19.20-patch/ (or/tmp)

Set the enviroment to the Target new home:

export ORACLE_HOME=/app_oracle/product/dbs/19.20
export PATH=/app_oracle/product/dbs/19.20/OPatch:$PATH
opatch version

cd /app_oracle/product/dbs/19.20-patch/35370174/35320081
opatch apply
cd /app_oracle/product/dbs/19.20-patch/35370174/35354406
opatch apply 
y
y
$ORACLE_HOME/OPatch/opatch lspatches

Oracle recommends to use the autoupgrade Tool to switch the home and install all correctly: You have to create an cfg to use autoupgrade correctly.

cd /app_oracle/local/tools/autoupgrade.jar

vi autoupgrade_patch_CDB.cfg

patch_C10P.log_dir=/app_oracle/log
patch_C10P.sid=C10P1
patch_C10P.source_home=/app_oracle/product/dbs/19.EE
patch_C10P.target_home=/app_oracle/product/dbs/19.20
patch_C10P.start_time=NOW
patch_C10P.run_utlrp=yes

Okay last Check – autoupgrades brings an „analyze“ with it

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar \
-config autoupgrade_patch_CDB.cfg \
-mode analyze

cat /app_oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

looks good -- everthing fine

Okay start the upgrade- now the database is a while unavailable – if it is a singe instance


$ORACLE_HOME/jdk/bin/java -jar /app_oracle/autoupgrade/autoupgrade.jar \
-config /app_oracle/autoupgrade/autoupgrade_patch_CDB.txt \
-mode deploy

cat /app_oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

Within our Sphinx Environment now its needed to change some things


cd /app_oracle/local/etc
Copy our oraenv specific shellscript too 

cp oraenv-dbs-19.EE.sh oraenv-dbs-19.20.sh

change the .EE with the current version
vi oraenv-dbs-19.20.sh -- change .EE mit .20

Relink oraenv-default.sh

rm oraenv-default.sh
ln -s oraenv-dbs-19.20.sh oraenv-default.sh

Switch to the current environment an take a look
chenv c10p
sqlplus "sys/sx123 as sysdba"

Also change listener.ora to run on 
ORACLE_HOME=/app_oracle/product/dbs/19.20
instead of 19.EE

-- 19.20.0.0 -- all is good

Worksfine, it can be used for x Databases in one Script

SAYS MIKE DIETRICH

Streamlined Oracle Installation Guide

In this concise blog post, we present a comprehensive Oracle Installation guide for VMWARE users, focusing on the seamless setup of Oracle Linux 8 (OL8) with Oracle Database 19c.

The journey begins with VMWARE ESXi, where we will lay the foundation for creating a robust Oracle environment. With our step-by-step instructions, you’ll find the process smooth and straightforward, allowing you to get your database up and running efficiently.

Stay tuned as we walk you through the entire installation process, unraveling the complexities and highlighting the essential elements to ensure a successful setup. Your Oracle Dataguard 19c implementation on OL8 in VMWARE will soon be a reality with our expert guidance.

Whether you’re a seasoned Oracle administrator or a newcomer to the world of databases, this blog post will provide valuable insights and help you harness the power of Oracle Dataguard for your VMWARE-based Oracle environment. Let’s embark on this journey together and unlock the potential of your data infrastructure!

  • CREATE AN VM
VMWARE Settings – 2 DISKs – 1 for OS 1 for Database ( i choose 256 GB for this, but 100 is also enough)
  • Install Linux

Upon selecting the OL 8 ISO image on the CD/DVD drive, we are now at the VM’s start screen.

OL 8 Startup in VMWARE ESXI Console

The main setting at this point is Language Support.

Also TIME & Date – Select Timezone Europe/Vienna


To rectify the incorrect selection, change the Network Settings Method to MANUAL.
Ask Tom for DNS Server and Gateway…

The next step is Software Selection.

You can add additional Software, but you dont need in our Setup

The next step is Installation Destination:

After this Process – change root PWD and start the Installationprocess.

After a while open putty and make ssh connection to the new created vm server:

  • Install Sphinx Environment Scripts for Linux + Oracle

Now, log in as root and follow these steps:

a) vi /etc/hosts 
add or change
10.xx.x.xxx tom.demo.internal.sphinx.at tom
b) vi /etc/hostname - check or rename host
c) vi /etc/sysconfig/network-scripts/i* - check or change ip

Let’s set up the volumes.

Look Logical Volumns
[root@tom]# lsblk

Create Physical Volumn and Volumn Group for sdb (Disk2)
pvcreate /dev/sdb
vgcreate app /dev/sdb

Create Logical Volumns 
lvcreate -L 16G -n app_others app 
lvcreate -L 16G -n app_oracle app
lvcreate -L 16G -n app_oracle_fra app
lvcreate -L 32G -n app_oracle_data app
lvcreate -L 16G -n app_oracle_exp app

Create XFS Filesystem
mkfs.xfs /dev/app/app_others
mkfs.xfs /dev/app/app_oracle
mkfs.xfs /dev/app/app_oracle_fra
mkfs.xfs /dev/app/app_oracle_data
mkfs.xfs /dev/app/app_oracle_exp

Create directorys
mkdir /app
mkdir /app_oracle
mkdir /app_oracle/data
mkdir /app_oracle/exp
mkdir /app_oracle/fra


And add mount points for it
mount /dev/app/app_others /app
mount /dev/app/app_oracle /app_oracle
mount /dev/app/app_oracle_data /app_oracle/data
mount /dev/app/app_oracle_exp /app_oracle/exp
mount /dev/app/app_oracle_fra /app_oracle/fra

Now it looks like this:
[root@tom /]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 16G 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 15G 0 part
├─ol-root 252:0 0 13.4G 0 lvm /
└─ol-swap 252:1 0 1.6G 0 lvm [SWAP]
sdb 8:16 0 32G 0 disk
├─app-app_oracle 252:2 0 16G 0 lvm /app_oracle
├─app-app_oracle_fra 252:3 0 4G 0 lvm /app_oracle/fra
├─app-app_oracle_data 252:4 0 5G 0 lvm /app_oracle/data
├─app-app_oracle_exp 252:5 0 756M 0 lvm /app_oracle/exp
└─app-app_others 252:6 0 6G 0 lvm /app

my host

It’s essential to add these mount points to the fstab file.

--now add via vi fstab all mount points to persists on reboot

vi /etc/fstab
/dev/mapper/app-app_others     /app             xfs     defaults        0 0
/dev/mapper/app-app_oracle     /app_oracle             xfs     defaults        0 0
/dev/mapper/app-app_oracle_data    /app_oracle/data             xfs     defaults        0 0
/dev/mapper/app-app_oracle_exp     /app_oracle/exp             xfs     defaults        0 0
/dev/mapper/app-app_oracle_fra     /app_oracle/fra             xfs     defaults        0 0

GitLab is an excellent choice for managing environment scripts.
First we need to install git:

yum install git
mkdir -p /media/software
cd /media/software
git clone https://gitlab.oursystem.at/demo/linux.git
git clone https://gitlab.oursystem.at/demo/oracle_root.git

-- install yum packages and other stuff we need in linux
cd linux
./install.sh
reboot

-- install yum packages and other stuff we need for oracle
cd ../oracle_root
./rootpre.sh -i oracle /app_oracle 19

Once the Oracle user is installed, you can proceed with a silent Oracle installation, eliminating the need for the Database Configuration Assistant (DBCA). This streamlined installation method will save time and effort, making the process more efficient.

YES
-- Get our enviromentscripts to handle Oracle easy
su - oracle

git clone https://gitlab.oursystem.at/demo/oracle_local.git local
cd local/etc
ln -s oraenv-dbs-19.EE.sh oraenv-default.sh
exit

su - oracle

We are ready to install Oracle Software 12c-23c now

THX SPHINX
su - oracle 
dbsw_install dbs 19.EE 19.EE 

mv $ORACLE_HOME/Opatch $ORACLE_HOME/Opatch_old

cd /app_oracle/local/tools
unzip p6880880_210000_Linux-x86-64.zip -d $ORACLE_HOME/

su - root

/app_oracle/product/dbs/19.EE/root.sh
/app_oracle/oraInventory/orainstRoot.sh

We are ready to install Oracle Database 12c-23c

THATS FINE
oranetinit [-f] [-L LDAP-server:port[:ssl-port] -L ...] sqlnet-domain wallet-password sys-password rman-password

Eg:
oranetinit demo.sphinx.at Sx123.45 sx123 sx123

And now DB Environment default settings:
mkoradbenv CDB 19.EE 1521

We ship some Scripts to handle the environment now for example:

chenv CDB -- change the enviroment settings
cd scripts

CAREFULLY: edit and change accordingly

init0.ora (change sqa blocksize aso...) 
changed: db_reco_file_dest to 3G

mkoracdb.par (plugdb add a name and a Pluggable DB will be created within the container and the listener will be notified to add a service_name)
changed: redosize = 100M und logmode = archivelog
mkoracdb.sql (only if required)

Create Database: using the command mkoracdb the database will be created as defined in the scripts from before.
It is advised to run this in a screen session as the process can take quite a while.

mkoracdb

Now you have to wait a little but.. until this the Database is installed

You can check the installation with

orasyscheck

Be happy (c) Sphinx

BEYOND THE LIMITS

Requirement: Every table of any schema should be editable.

  • Choose a schema
  • Choose a table
  • Select any columns
  • Generate an editing mask

So, I would say it’s something dynamic, but it works… see for yourself:

Imagine a scenario where you have a database with multiple schemas, each containing various tables and columns. Now, let’s say you have a requirement where you need to allow users to edit the data in any table of any schema. This can be quite a challenging task, considering the dynamic nature of the database structure. However, with the right approach, it is definitely achievable.

To begin with, the first step is to select the desired schema. This can be done by providing a dropdown or a list of available schemas for the user to choose from. Once a schema is selected, the next step is to choose a specific table within that schema. Again, you can provide a dropdown or a list of tables available in the selected schema for the user to pick from.

Now comes the interesting part. The user should be able to select any columns they want to edit within the chosen table. This can be done by displaying all the columns of the selected table and allowing the user to check or uncheck the ones they wish to include in the editing process. This flexibility gives the user full control over the data they want to modify.

Once the user has made their column selections, it’s time to generate an editing mask. The editing mask is a dynamic interactive grid that displays the selected columns as editable fields. It provides a convenient way for the user to modify the data within those columns. The mask should reflect the data type and constraints of each column, ensuring that the modifications are valid and consistent with the database schema.

By following these steps, you can create a dynamic solution that allows users to edit data in any table of any schema. This kind of flexibility empowers users to manage and manipulate their data efficiently, making your database application more user-friendly and versatile.

https://www.sphinx.at/blog/wp-content/uploads/2023/07/Generative_Interactive_GRID2.gif

animated



Die Migration von Oracle Forms zu APEX: 5 Vorteile und 5 Risiken

Einleitung:

In der sich ständig weiterentwickelnden Welt der Technologie ist es für Unternehmen unerlässlich, ihre Anwendungen auf dem neuesten Stand zu halten. Eine Technologie, die in den letzten Jahren in den Fokus gerückt ist, ist Oracle Application Express (APEX), eine Plattform, die als vielversprechende Option für die Modernisierung von Oracle Forms Anwendungen gilt. In diesem Beitrag werden wir die fünf Hauptvorteile und Risiken der Migration von Oracle Forms zu APEX untersuchen.

Vorteile der Migration zu APEX:

  1. Leichtgewichtige Architektur: APEX ist eine Plattform, die in der Cloud oder On-Premises betrieben werden kann. Dies bietet Flexibilität und erleichtert die Integration in bestehende Infrastrukturen.
  2. Einfache Bedienung: APEX bietet eine benutzerfreundliche Oberfläche und eine einfache Programmierung, die sowohl Entwicklern als auch Endbenutzern zugänglich ist.
  3. Skalierbarkeit: APEX ist auf Skalierbarkeit ausgelegt und kann problemlos für kleine oder große Unternehmen verwendet werden. Es kann auf eine Vielzahl von Datenquellen zugreifen und ermöglicht die Integration von Drittanbieter-Tools.
  4. Sicherheit: APEX bietet robuste Sicherheitsfunktionen, darunter eine rollenbasierte Zugriffskontrolle, Schutz vor SQL-Injektion Angriffen und SSL-Unterstützung.
  5. Kosteneffizienz: Im Vergleich zu Oracle Forms ist APEX in den meisten Fällen kostengünstiger, insbesondere wenn eine bestehende Oracle-DB-Infrastruktur vorhanden ist.

Risiken der Migration zu APEX:

  1. Komplexe Architektur: Oracle-Forms-Anwendungen haben oft eine komplexe Architektur, die sich über Jahre hinweg entwickelt hat. Die Modernisierung erfordert ein tiefes Verständnis der Architektur und der verwendeten Technologien.
  2. Fehlende Dokumentation: Oft fehlt es an ausreichender Dokumentation, um das Verständnis der Architektur und der Funktionalität der Anwendung zu erleichtern. Dies kann die Modernisierung erschweren und den Prozess verlangsamen.
  3. Inkompatible Technologien: Oracle Forms wurde entwickelt, bevor moderne Web- und Mobiltechnologien aufkamen. Die Anwendung ist daher in der Regel nicht mit diesen Technologien kompatibel. Die Modernisierung erfordert oft eine umfassende Überarbeitung der Benutzeroberfläche und der Backend-Architektur.
  4. Komplexität der Datenmigration: Bei der Modernisierung von Oracle-Forms-Anwendungen kann die Datenmigration eine Herausforderung darstellen. Die Daten müssen oft in ein neues Datenbankschema migriert werden, was Zeit und Ressourcen erfordert.
  5. Schulung der Mitarbeiter: Die Migration von Oracle Forms nach APEX erfordert eine Umschulung der Entwickler, um sicherzustellen, dass sie die neuen Technologien verstehen und effektiv nutzen können.

Fazit

Die Migration von Oracle Forms zu APEX bietet eine Reihe von Vorteilen, darunter eine leichtgewichtige Architektur, einfache Bedienung, Skalierbarkeit, verbesserte Sicherheit und Kosteneffizienz. Gleichzeitig gibt es jedoch auch Risiken, wie die Komplexität der vorhandenen Architektur, fehlende Dokumentation, Inkompatibilität mit modernen Technologien, die Herausforderung der Datenmigration und die Notwendigkeit der Umschulung von Entwicklern. Daher ist es wichtig, dass Unternehmen eine sorgfältige Planung und Vorbereitung durchführen, um sicherzustellen, dass die Migration erfolgreich ist. Mit der richtigen Strategie und den richtigen Ressourcen können Unternehmen jedoch die Vorteile von APEX voll ausschöpfen und ihre Anwendungen erfolgreich modernisieren.

Oracle Apex 23.1

Woohooo

Beste Apex Release ever is here..

Oracle APEX 23.1 has introduced a number of new features and improvements that enhance and expand the development experience. Here are some of the highlighted features:

  1. Template Components Unleashed: With APEX 23.1’s Template Components, building UI components is made easy and reusable. They can be rendered as standalone regions or within reports, and they support actions, menus, and custom attributes.
  2. PWA Push Notifications: The push notifications keep users updated whether they’re on desktop or mobile. They allow for easy subscription management and a manageable queue of notifications. The setup is very quick and straightforward.
  3. Developer Experience: The improvements to the Object Browser in APEX 23.1 offer a modernized design and enhanced editing experience. It also has improved performance and accessibility.
  4. Page Processing Improvements: With the new Page Process Type – Execution Chains, you can keep your page processes organized and under control. You can now monitor and report on running background executions, making your life as an APEX developer more efficient and enjoyable.
  5. REST Data Source Enhancements: With the REST Data Source Enhancements, you can invoke API for REST Sources, discover them with Swagger/OpenAPI, and enjoy the added flexibility of Raw Selectors.
  6. General Builder Improvements: With the General Builder Improvements, you can now copy pages from Create App, save and run pages from Code Editor, and access context-sensitive help. There is also native support for Property Graphs in Database 23c.
  7. APEX Approvals: With the Approvals component in APEX 23.1, you can keep your approval tasks on track by specifying due dates when creating them.
  8. Universal Theme and UX Improvements: The Universal Theme in APEX 23.1 has been updated and improved. It offers improved Template Components, improved icon fidelity, and an enhanced Region Display Selector.

These improvements and features contribute to making APEX an even more powerful platform for developing enterprise applications.

Exasol Virtual Schema Performancetest

In today’s world, where companies have to deal with huge amounts of data, the issue of data management efficiency becomes more and more important. One of the solutions to optimize data management and improve performance is to use Virtual Schemas in Exasol, a high-performance in-memory database.

In the following, we present a performance test of Exasol Virtual Schema to reduce the amount of data in an Exasol cluster environment. The idea behind this technique is to use a smaller Exasol cluster with a storage (memory) license and offload data to it.

Offloading data to a smaller cluster can lead to better performance by reducing the amount of active data that needs to be stored on the main Exasol instance. It also allows for greater flexibility by not having to keep the data on the main cluster all the time, resulting in better use of resources.

To perform this test, we use the Virtual Schema Adapter, which is written in Java. This adapter allows us to interact with Exasol via JDBC.

First we create the schema and the adapter:

CREATE SCHEMA X_SCHEMA_FOR_VS_SCRIPT;
CREATE OR REPLACE JAVA ADAPTER SCRIPT X_SCHEMA_FOR_VS_SCRIPT.ADAPTER_SCRIPT_EXASOL AS
    %scriptclass com.exasol.adapter.RequestDispatcher;
    %jar /buckets/bfsdefault/vschema/virtual-schema-dist-10.5.0-exasol-7.1.1.jar;
/

We then define two connections to our Exasol instance, one JDBC and one native Exasol connection:

CREATE OR REPLACE CONNECTION JDBC_CONNECTION_EXA_DEV1 
TO 'jdbc:exa:1.112.32.331..333/FINGERPRINT:8565'
USER 'SYS'
IDENTIFIED BY 'xx';
CREATE OR REPLACE CONNECTION EXA_CONNECTION_DEV1
TO '1.112.32.331..333/FINGERPRINT:8565'
USER 'SYS'
IDENTIFIED BY 'xx';

After that we create the Virtual Schema with the JDBC connection we created earlier:

CREATE VIRTUAL SCHEMA VIRTUAL_EXASOL_DEV1 
USING  X_SCHEMA_FOR_VS_SCRIPT.ADAPTER_SCRIPT_EXASOL WITH
    CONNECTION_NAME = 'JDBC_CONNECTION_EXA_DEV1'
    SCHEMA_NAME     = 'HISTORY_ARCHIVE'  
    IMPORT_FROM_EXA = 'true'
    EXA_CONNECTION  = 'EXA_CONNECTION_DEV1'
    MAX_TABLE_COUNT = '10000';

After that, the data will be merged with the data from the other source:

Since we do not want that another query is necessary than before. It should remain transparent for our customer!

create or replace view FULL_DATA
as select * from ACTUAL_DATA 
where datadate > sysdate-interval '1' MONTH
union all 
select * from VIRTUAL_EXASOL_DEV1.ARCHIVE_DATA 
where datadate < sysdate- interval '1' MONTH;

When we do a performance test we see that there is a clear difference if we do a query on a large database or on 2 databases when they are linked via Union All and Virtual Schema.

DEV1 is really slow – but cheap!

The optimizer of course passes the Where Clause, but still fetches all the necessary data over the network. And that takes time. Note: Runtime in seconds.

Conclusion: Depending on the query, the query performance has an effect. If you only need a few data for a calculation, it is of course not so dramatic, but we achieved a slowness factor between 11 upto 157 in our tests. Since we only tested with simple queries, this is still an important knowhow for us.

SPHINX.AT

YAITCON

« Older posts

© 2023 Sphinx Blog

Theme by Anders NorenUp ↑