.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in Oracle Database :::.

1. create table 

CREATE TABLE datefilter (
    datex DATE NOT NULL
);

CREATE TABLE datefilter (
    datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '1' SECOND
FROM cte;

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '15' DAY + INTERVAL '1' SECOND
FROM cte;

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in PostgreSQL EDB :::.

 

1. create table 

CREATE TABLE datefilter (
    datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

WITH RECURSIVE cte AS (
    -- Mulai dari bulan ini
    SELECT date_trunc('month', now())::date AS dt,
           1 AS lvl

    UNION ALL
    
    -- Mundur 36 bulan
    SELECT (dt - INTERVAL '1 month')::date,
           lvl + 1
    FROM cte
    WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT dt + INTERVAL '1 millisecond'             -- tanggal 1
FROM cte
UNION ALL
SELECT dt + INTERVAL '14 days' + INTERVAL '1 millisecond'   -- tanggal 15
FROM cte;

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in MarinaDB MariaDB :::.

 

1. create table 

CREATE TABLE datefilter (
    datex DATETIME NOT NULL
);

2. create date sample 3 years
INSERT INTO datefilter (datex)
WITH RECURSIVE cte AS (
    -- Mulai dari bulan ini (tanggal 1)
    SELECT
        DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AS dt,
        1 AS lvl

    UNION ALL

    -- Mundur 36 bulan
    SELECT
        DATE_SUB(dt, INTERVAL 1 MONTH),
        lvl + 1
    FROM cte
    WHERE lvl < 36
)
SELECT dt + INTERVAL 1000 MICROSECOND         -- tanggal 1
FROM cte

UNION ALL

SELECT (dt + INTERVAL 14 DAY) + INTERVAL 1000 MICROSECOND   -- tanggal 15
FROM cte;
 

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in SQL Server MSSQL :::.

 

1. create table 

CREATE TABLE datefilter (
    datex DATETIME NOT NULL
);

2. create date sample 3 years

;WITH cte AS (
    -- Mulai dari bulan ini
    SELECT 
        CAST(DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS DATETIME) AS dt,
        1 AS lvl
    UNION ALL
    -- Mundur 36 bulan (3 tahun)
    SELECT 
        DATEADD(MONTH, -1, dt),
        lvl + 1
    FROM cte
    WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT DATEADD(MILLISECOND, 1, dt)              -- Tanggal 1
FROM cte
UNION ALL
SELECT DATEADD(MILLISECOND, 1, DATEADD(DAY, 14, dt))   -- Tanggal 15
FROM cte
OPTION (MAXRECURSION 0);

.::: Script Monitoring GTID Mirroring using Shell Script Colour :::.

 

1. Monitoring GTID no colour
 

 

.::: Install MariaDB 10.6, Maxscale, Keepalived in Suselinux SLES 15 :::.

 

1. enable repo 

teguhth01suse:~ # cat /etc/zypp/repos.d/mariadb.repo
[mariadb]
enabled=1
autorefresh=1
baseurl=https://yum.mariadb.org/10.6/sles/15/x86_64
gpgcheck=1
teguhth01suse:~ #

2. Prepare packages
rpm -qa | grep -i libmaria

zypper remove libmariadb3-3.1.22-150600.16.3.x86_64

rpm -qa | grep -i libmaria

rpm --import https://downloads.mariadb.com/MariaDB/MariaDB-Server-GPG-KEY
rpm --import https://downloads.mariadb.com/MariaDB/RPM-GPG-KEY-MariaDB

wget https://ftp.lysator.liu.se/pub/opensuse/update/leap/15.6/sle/x86_64/socat-1.8.0.0-150600.20.6.1.x86_64.rpm
zypper install socat-1.8.0.0-150600.20.6.1.x86_64.rpm

wget https://ftp.lysator.liu.se/pub/opensuse/update/leap/15.6/sle/x86_64/libasan8-14.3.0+git11799-150000.1.11.1.x86_64.rpm
zypper install libasan8-14.3.0+git11799-150000.1.11.1.x86_64.rpm

.::: Install MariaDB 10.6 in Debian 11 :::.

 

1. add update debian 

root@debian02:~# cat /etc/apt/sources.list
# deb cdrom:[Debian GNU/Linux 11.6.0 _Bullseye_ - Official amd64 DVD Binary-1 20221217-10:40]/ bullseye contrib main

#deb cdrom:[Debian GNU/Linux 11.6.0 _Bullseye_ - Official amd64 DVD Binary-1 20221217-10:40]/ bullseye contrib main

#deb http://security.debian.org/debian-security bullseye-security main contrib
#deb-src http://security.debian.org/debian-security bullseye-security main contrib

# bullseye-updates, to get updates before a point release is made;
# see https://www.debian.org/doc/manuals/debian-reference/ch02.en.html#_updates_and_backports
# A network mirror was not selected during install.  The following entries
# are provided as examples, but you should amend them as appropriate
# for your mirror of choice.
#
# deb http://deb.debian.org/debian/ bullseye-updates main contrib
# deb-src http://deb.debian.org/debian/ bullseye-updates main contrib

deb http://deb.debian.org/debian bullseye main contrib non-free
deb-src http://deb.debian.org/debian bullseye main contrib non-free

deb http://security.debian.org/debian-security bullseye-security main contrib non-free
deb-src http://security.debian.org/debian-security bullseye-security main contrib non-free

deb http://deb.debian.org/debian bullseye-updates main contrib non-free
deb-src http://deb.debian.org/debian bullseye-updates main contrib non-free
root@debian02:~#

.::: Script Drop All Database non-system in MariaDB MySQL in Centos 9 :::.

 

1. run script drop database non-system 

sh drop_database_nosystem_all.sh
 

2. run script drop database non-system with duration

sh drop_database_nosystem_all_duration.sh 

.::: Backup Database MariaDB with encripted -aes-256-cbc dan restore mariadb database :::.

1. backup enkripsi

mysqldump -uroot -pxxx teguhth | openssl enc -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc
mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc
mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc | gzip -c  > teguhthenc.gz.sql.enc
 
 

2. convert to sql from enkripsi file to sql
openssl enc -d -aes-256-cbc -md sha1 -in teguhthenc.sql.enc -out teguhth.sql -pass pass:abcde
 

.::: Script restore database MariaDB using record to table include duration Restore :::.

 

A. simple record

1. create table restore_log

CREATE TABLE IF NOT EXISTS restore_log (
    db_name VARCHAR(100),
    restore_time DATETIME,
    backup_file VARCHAR(255),
    restored_by VARCHAR(100)
);

2. create script 

.::: Script Backup Full & Incremental All Databases Using Record to Table include start, finish & duration backup in PostgreSQL EDB :::.

  

https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
https://teguhth.blogspot.com/2025/09/script-backup-full-incremental-all.html

1. create database & table

CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Create CDB & Sample Database in Oracle 19C :::.

 

1. Login sebagai SYS atau SYSTEM

Masuk ke CDB lalu kasih privilege ke HRADMIN:

-- Masuk ke CDB sebagai SYS
sqlplus sys@//localhost:1521/TGHDB as sysdba

-- Pindah ke PDB TGHDBHR
ALTER SESSION SET CONTAINER=TGHDBHR;

-- Beri hak penuh

CREATE USER hradmin IDENTIFIED BY hrpass;
GRANT CONNECT, RESOURCE TO hradmin;
ALTER USER hradmin QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO hradmin;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO hradmin;

-- Kalau mau full DBA di PDB
GRANT DBA TO hradmin;

sqlplus hradmin/hrpass@//localhost:1521/TGHDBHR
 

.::: Create New Container or add Container CDB in Oracle 19C :::.

 

1. Cek dulu container yang ada

sqlplus sys/oracle as sysdba;

SHOW CON_NAME;
SHOW PDBS;
 

2. Buat PDB baru dari PDB$SEED (template bawaan)

Misalnya mau buat container baru bernama TGHDBHR:

CREATE PLUGGABLE DATABASE TGHDBHR
  ADMIN USER hradmin IDENTIFIED BY hrpass
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/TGH/pdbseed/',
                     '/u01/app/oracle/oradata/TGH/TGHDBHR/');

.::: First time login Oracle using CDB (Container Database / Multitenant) :::.

 

1. login as sysdba 
sqlplus sys/oracle as sysdba;

oracle@teguhth ~]$ sqlplus sys/oracle as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 24 14:12:31 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
 

.::: Script Backup Full & Incremental All Database Using Record to Table in PostgreSQL EDB :::.

correlation with https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
correlation with https://teguhth.blogspot.com/2025/01/script-backup-full-all-database-using.html

1. create table 

-- Table: public.backuplogs

CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date TIMESTAMP NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    backup VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Script to Restore Backup Full & all Transaction (Restore Incremental) in one Command for EnterpriseDB PostgreSQL :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-to-restore-backup-full-all.html
correlation https://teguhth.blogspot.com/2025/09/script-daily-backup-full-incremental.html

1. check before restore
 
2. check directory backup & create dblist,txt
[postgres@teguhth dwh]$ pwd
/var/lib/pgsql/16/manual/dwh
[postgres@teguhth dwh]$
[postgres@teguhth dwh]$ cat dblist.txt
FULL_dwh_20250913_000001.sql.gz
INC_dwh_20250913_010002.sql.gz
INC_dwh_20250913_020002.sql.gz
INC_dwh_20250913_030002.sql.gz
INC_dwh_20250913_040002.sql.gz
INC_dwh_20250913_050002.sql.gz
INC_dwh_20250913_060002.sql.gz
INC_dwh_20250913_070001.sql.gz
INC_dwh_20250913_080002.sql.gz
INC_dwh_20250913_090002.sql.gz
INC_dwh_20250913_100001.sql.gz
INC_dwh_20250913_110002.sql.gz
INC_dwh_20250913_120002.sql.gz
INC_dwh_20250913_130001.sql.gz
INC_dwh_20250913_140002.sql.gz
INC_dwh_20250913_150002.sql.gz
INC_dwh_20250913_160001.sql.gz
INC_dwh_20250913_170002.sql.gz
INC_dwh_20250913_180002.sql.gz
INC_dwh_20250913_190001.sql.gz
INC_dwh_20250913_200002.sql.gz
INC_dwh_20250913_210002.sql.gz
INC_dwh_20250913_220002.sql.gz
INC_dwh_20250913_230001.sql.gz
[postgres@teguhth dwh]$
 

.::: Script Daily Backup FULL & Incremental PostgreSQL EDB Using pgdump & pgaudit :::.

 

correlation with https://teguhth.blogspot.com/2025/09/how-to-create-backup-incremental-in.html

1. Backup All FULL
 

 

.::: How to Create Backup Incremental in PostgreSQL EDB as Logical Backup (like SQL Server or MariaDB with mysqlbinlog) using pgaudit pgauditlogtofile :::.

 

A. Activate pgaudit 
correlation https://teguhth.blogspot.com/2024/02/enable-pgaudit-pgauditlogtofile-in.html

B. convert to table 

correlation https://teguhth.blogspot.com/2024/04/convert-pgaudit-pgauditlogtofile-log.html

C. Testing sample CRUD Activity (sample database 'dwh')

1. create table pembelian;

create table barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));
  

.::: Install MariaDB GTID Mirroring Monitoring using php :::.

 

install php https://infotechys.com/install-php-8-3-on-rhel-9-centos-9/

1. create bash monitoring 

[root@teguhth03 web]# cat /data/web/server31.sh
#!/bin/bash

# Variabel koneksi
USER="admin"
PASS="xxxxx"
HOST="10.10.10.31"
PORT="3306"

# Test koneksi ke MySQL server
mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -e "SELECT 1;" >/dev/null 2>&1

 if [ $? -ne 0 ]; then
    echo "{ \"connections\": \"\", \"role\": \"Down\", \"gtid\": \"\",\"hostname\": \"$hostnamex\", \"host\": \"$HOST\", \"port\": \"$PORT\",\"dbv\": \"$versionx\",\"osv\": \"$osx\" }"
    exit 0
fi
 

.::: Install Microsoft SQL Server 2022 in Centos 9 / Redhat 9 / Almalinux 9 :::.

 

1. disable selinux

2. enable repo 

curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/$(rpm -E %{rhel})/mssql-server-2022.repo

curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

curl https://packages.microsoft.com/config/rhel/9/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

[root@teguhth data]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/$(rpm -E %{rhel})/mssql-server-2022.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   444  100   444    0     0    199      0  0:00:02  0:00:02 --:--:--   199
[root@teguhth data]#
[root@teguhth data]#
[root@teguhth data]# cat /etc/yum.repos.d/mssql-server.repo
[packages-microsoft-com-mssql-server-2022]
name=Microsoft SQL Server 2022
baseurl=https://packages.microsoft.com/rhel/9/mssql-server-2022/
enabled=1
gpgcheck=1
repo_gpgcheck=1
# If installing the bare repo file consider installing the gpg key once and then updating
# this to point to the on-disk location. For example:
# gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Microsoft
gpgkey=https://packages.microsoft.com/keys/microsoft.asc
sslverify=1
[root@teguhth data]#

.::: Install MariaDB 10.6 in Ubuntu 22 :::.

  

1. Tambahkan GPG Key mariadb

sudo apt install -y curl software-properties-common gnupg
curl -fsSL https://mariadb.org/mariadb_release_signing_key.asc | sudo gpg --dearmor -o /usr/share/keyrings/mariadb-keyring.gpg

2. Tambahkan Repository mariadb 10.6

deb [arch=amd64,arm64 signed-by=/usr/share/keyrings/mariadb-keyring.gpg] http://mirror.mariadb.org/repo/10.6/ubuntu noble main
 

.::: Script Backup & Restore MariaDB using Confirm before Action Execute :::.

 

A. Action 

1. test restore not found
 

 

.::: Query Check day, hour & early time, check time, different time in MSSQL, MariaDB MySQL, PostgreSQL EDB, Oracle DB to Get the Start of the Current Hour and the Previous Hour :::.

  

A. MSSQL

1. one day before

select @@servername as ServerName, GETDATE() as Currentdate, CAST(CONVERT(date, GETDATE() - 1) AS datetime) as datebefore , CAST(CONVERT(date, GETDATE()) AS datetime) as datecurrent 

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= CAST(CONVERT(date, GETDATE() - 1) AS datetime) 
  AND tanggal <  CAST(CONVERT(date, GETDATE()) AS datetime);

.::: Simulation Query Select from Java to MariaDB using mariadb-java-client-3.5.3.jar (MariaDB Driver) using MariaDB SSL Certificate :::.

 

correlation https://teguhth.blogspot.com/2025/06/simulation-query-select-from-java-to.html
https://teguhth.blogspot.com/2025/06/simulation-connection-java-to-mariadb.html
    
1. Konversi client-cert.pem dan client-key.pem ke .p12

 

openssl pkcs12 -export -in /home/user/certs/client-cert.pem -inkey /home/user/certs/client-key.pem -out /home/user/certs/client-cert.p12 -name client -CAfile /home/user/certs/ca-cert.pem -caname root -passout pass:root

.::: Create User MariaDB MySQL using SSL / Certificate :::.

 

Mengaktifkan TLS/SSL pada MariaDB 10.6 dan jika sudah nanti akan di test dengan user 'adminssl' password 'adminssl' 

Berikut adalah panduan mengaktifkan TLS/SSL di MariaDB 10.6 dan menguji koneksi dengan user adminssl:

1. Buat Direktori & Sertifikat SSL

mkdir -p /etc/mysql/ssl

cd /etc/mysql/ssl

2. Create Certificate 
 

.::: Script Daily Backup MongoDB using Date or Compress :::.

  

1. Script backup full 

#[root@teguhth mongod]# cat backup_mongodb_full_script.sh
##-bash-4.2$ cat backup_mongodb_full_script.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
 

.::: Sample shell script integration MariaDB to Redis :::.

 

1. run script using mariadb
sh compare_maria_redis.sh mariadb
 

2. run script using redis
sh compare_maria_redis.sh redis
 

.::: Install MongoDB 8 in Centos 9 (mongodb-org-server, mongodb-mongosh) :::.

  

1. download

wget https://repo.mongodb.org/yum/redhat/9/mongodb-org/8.0/x86_64/RPMS/mongodb-org-server-8.0.10-1.el9.x86_64.rpm

2. create repository

[root@teguhth ~]# cat /etc/yum.repos.d/mongodb-org.repo
[mongodb-org-6.0]
name=MongoDB Repository
#baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/6.0/x86_64/
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/9.0/x86_64/
gpgcheck=0
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-6.0.asc
[root@teguhth ~]#

.::: Start the SQL Server Import and Export Wizard From MariaDB to MSSQL using Write a query to specify the data to transfer & compare with Open Query :::.

 

correlation with https://teguhth.blogspot.com/2025/06/start-sql-server-import-and-export.html

A. Prepare

1. MariaDB check sample query 

select * from products where product_name='laptop' or product_name='tablet';

2. create view mariadb 

create view view_produk select * from products where product_name='laptop' or product_name='tablet';

3. test if using open query

select * from openquery([MARIALOCAL],'select * from erpdb.view_produk;')
select * from openquery([MARIALOCAL],'select * from erpdb.products where product_name=''laptop'' or product_name=''tablet'';')
  

.::: Start the SQL Server Import and Export Wizard From MSSQL to MariaDB :::.

 

1. Install ODBC Driver MariaDB MySQL (32 bits) for this case install mysql-connector-odbc-8.0.42-win32

mysql-connector-odbc-8.0.42-win32
mariadb-connector-odbc-3.2.6-win32

2. Setup DSN MariaDB Using MySQL Driver 

# using mysql driver 32 >> pakai yang ini 
Dsn=mysql321;Driver={MySQL ODBC 8.0 Unicode Driver};server=10.10.10.7;uid=admin;pwd=admin;database=erpdb;port=3306

# using maridb driver32
Dsn=maria;Driver={MariaDB ODBC 3.2 Driver};server=10.10.10.7;uid=admin;pwd=admin;database=erpdb;port=3306
 

.::: Shell Script to compare table Database MariaDB MySQL :::.

 

  correlation with https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

A. Compare Multiple Table 
1. check table server1
 

 

.::: Shell Script to compare value table a Database MariaDB MySQL :::.

  

correlation with https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

1. check value table barang server1
 

 

.::: Trial Backup & Restore MariaDB MySQL with crontab -l / crontab -e & /etc/crontab :::.

 

A. Prepare Script backup & Restore

1. create backup script

[root@teguhth cron]# pwd
/data/cron
[root@teguhth cron]# cat backup_test.sh
echo "backup full mulai" > log_backup.txt
date >> log_backup.txt

mysqldump -uroot -pxxx -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false teguhth | gzip -c > teguhth_ori.sql.gz ;

echo "backup full selesai " >> log_backup.txt
date  >> log_backup.txt
[root@teguhth cron]#

.::: Simulation Query Select from Java to MariaDB using mariadb-java-client-3.5.3.jar (MariaDB Driver) :::.

  

https://teguhth.blogspot.com/2025/06/simulation-connection-java-to-mariadb.html

1. Script java 

[root@teguhth maria]# cat queryjoin.java
import java.sql.*;

public class queryjoin {
    public static void main(String[] args) {
        String url = "jdbc:mariadb://10.10.10.90:3306/teguhth"; // Ganti IP jika perlu
        String user = "admin";
        String pass = "admin";

        try {
            Connection conn = DriverManager.getConnection(url, user, pass);

            // Jalankan query
//            String query = "select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;";

//String query = "SELECT b.NAMA_BARANG, s.NAMA_SUPLIER, p.TANGGAL_PASOK, p.JUMLAH_PASOK, (SELECT SLEEP(20)) as DELAY FROM barang b, suplier s, pasok p WHERE b.KODE_BARANG = p.KODE_BARANG AND s.KODE_SUPLIER = p.KODE_SUPLIER;";

//String query ="select * from pembelian;";
 

.::: Simulation connection java to MariaDB for Success & Fail using mysql-connector-j-8.0.33.jar (MySQL Driver) (com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) :::..

 

1. Download (Platform independent) MySQL Driver

https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar

java -version
dnf install java-17-openjdk-devel -y    # CentOS 9

2. check login mariadb

[root@teguhth ~]# mysql -h 10.10.10.90 -uadmin -p teguhth
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 11.4.4-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

.::: Simulation connection java to MariaDB for Success & Fail using mariadb-java-client-3.5.3.jar (MariaDB Driver) (java.sql.SQLNonTransientConnectionException: Socket fail to connect to 10.10.10.9. No route to host) :::.

 

1. Download (Platform independent)

https://mariadb.com/downloads/connectors/connectors-data-access/java8-connector
 

java -version
dnf install java-17-openjdk-devel -y    # CentOS 9

2. check login mariadb

[root@teguhth ~]# mysql -h 10.10.10.90 -uadmin -p teguhth
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 11.4.4-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

.::: Move TempDB to other folder (move SQL Server MDF and LDF Files location):::.

 

 
correlation https://teguhth.blogspot.com/2023/06/move-sql-server-mdf-and-ldf-files.html

1. check 

sp_helpdb tempdb

USE tempdb;
GO
SELECT 
    name AS [Logical Name],
    physical_name AS [File Path],
    type_desc AS [File Type],
    size * 8 / 1024 AS [Size (MB)],
    max_size,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

.::: Quary query to simulation High CPU & High Memory Usage In EDB PostgreSQL :::.

 

 
A. Simulation High CPU

1. Contoh query untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di PostgreSQL menggunakan query, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage PostgreSQL tinggi, gunakan di lingkungan development/test, bukan production.


CREATE OR REPLACE PROCEDURE cpu_hog()
LANGUAGE plpgsql
AS $$
DECLARE
    start_time timestamp := clock_timestamp();
    elapsed interval;
    i integer := 0;
    j double precision := 0;
BEGIN
    LOOP
        j := sqrt(i * random());
        i := i + 1;

        elapsed := clock_timestamp() - start_time;
        EXIT WHEN elapsed >= interval '20 seconds';
    END LOOP;
END;
$$;