.::: Backup & Restore database IBM-DB2 DB2 :::.

 1. Introduction
Backup and recovery methods are designed to keep our information safe. In Command Line Interface (CLI) or Graphical User Interface (GUI) using backup and recovery utilities you can take backup or restore the data of databases in DB2 UDB.

Logging
Log files consist of error logs, which are used to recover from application errors. The logs keep the record of changes in the database. There are two types of logging as described below:

Circular logging
It is a method where the old transaction logs are overwritten when there is a need to allocate a new transaction log file, thus erasing the sequences of log files and reusing them. You are permitted to take only full back-up in offline mode. i.e., the database must be offline to take the full backup.

Archive logging
This mode supports for Online Backup and database recovery using log files called roll forward recovery. The mode of backup can be changed from circular to archive by setting logretain or userexit to ON. For archive logging, backup setting database require a directory that is writable for DB2 process.
 

.::: insert Query to test Replica SQL Server from Publisher to Subscription :::.

 continue from http://teguhth.blogspot.com/2021/10/how-to-install-replica-sql-server-mssql.html

1. run query
-- run in primary/publisher
select @@SERVERNAME as ServerName,* from raju.dbo.pembelian;

-- run in subscription
select @@SERVERNAME as ServerName,* from raju_replica.dbo.pembelian

.::: Create user, change password, change role & drop user , create user with specification limited access PostgreSQL EDB :::.

1. Create user

su - postgres

create user admin;
ALTER ROLE admin SUPERUSER CREATEDB CREATEROLE REPLICATION;
ALTER ROLE admin PASSWORD 'admin';
ALTER USER admin WITH PASSWORD 'admin';

DROP user admin;

[root@teguhth ~]# su - postgres
Last login: Thu Oct 13 15:13:33 +07 2022 on pts/0
-bash-4.2$ psql
psql (9.2.24, server 14.5)
WARNING: psql version 9.2, server version 14.0.
         Some psql features might not work.
Type "help" for help.

postgres=#

postgres=# create user testuser;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication             | {}
 myuser    |                                                            | {}
 pgb       | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 teguh     | Superuser, Create role, Create DB, Replication             | {}
 testuser  |                                                            | {}

.::: Query Check SQL Server Uptime MSSQL :::.

 1. Check

SELECT @@servername as servername,getdate() as TimeCollect,sqlserver_start_time, DATEDIFF(Day, sqlserver_start_time,getdate()) as sql_uptime_day ,
DATEDIFF(hour, sqlserver_start_time,getdate()) as sql_uptime_hours,DATEDIFF(minute, sqlserver_start_time,getdate()) as sql_uptime_minutes,
DATEDIFF(second, sqlserver_start_time,getdate()) as sql_uptime_second,DATEDIFF(millisecond, sqlserver_start_time,getdate()) as sql_uptime_milisecond
FROM sys.dm_os_sys_info;

.::: Install IBM DB2 In Windows :::.

 

.::: Install IBM DB2 In Linux Centos 7 / Redhat 7 & check version Db2 :::.

 
1. Extract * install DB2

[db2inst1@teguhth-grid ~]$ cd /data/
[db2inst1@teguhth-grid data]$ ls
db2v11.5.7_linuxx64_server_dec.tar  server_dec
[db2inst1@teguhth-grid data]$
[db2inst1@teguhth-grid data]$ ls server_dec/
db2  db2checkCOL_readme.txt  db2checkCOL.tar.gz  db2ckupgrade  db2_deinstall  db2_install  db2ls  db2prereqcheck  db2setup  installFixPack
[db2inst1@teguhth-grid data]$

[root@teguhth-grid server_dec]# ./db2setup

password root root

 

 

.::: Study Kasus Praktis belajar Query create & insert table IBM DB2 (Create Data Warehouse) :::.

1. create user db2 in linux

[root@teguhth-grid ~]# useradd teguh
[root@teguhth-grid ~]# passwd teguh
Changing password for user teguh.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@teguhth-grid ~]#

2. login to user db2 linux

[root@teguhth-grid ~]# su - db2inst1
Last login: Sun Oct  2 10:29:47 WIB 2022 on pts/0
[db2inst1@teguhth-grid ~]$ db2 grant connect on database to user teguh
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
[db2inst1@teguhth-grid ~]$ 

[db2inst1@teguhth-grid ~]$ db2 connect to teguhth
[db2inst1@teguhth-grid ~]$ db2 grant connect on database to user teguh
[db2inst1@teguhth-grid ~]$ db2 grant dbadm on database to user teguh
 

.::: How to identify DB2 port number Service & connect IBM Data Studio include error :::.

1. check for windows
db2 get database manager configuration | findstr /i svce

C:\Program Files\IBM\SQLLIB\BIN>db2 get database manager configuration | findstr
 /i svce
 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 SSL service name                         (SSL_SVCENAME) =

C:\Program Files\IBM\SQLLIB\BIN>


C:\Program Files\IBM\SQLLIB\BIN>db2 get dbm cfg | findstr "SVCENAME"
 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 SSL service name                         (SSL_SVCENAME) =

C:\Program Files\IBM\SQLLIB\BIN>
C:\Program Files\IBM\SQLLIB\BIN>

C:\Program Files\IBM\SQLLIB\BIN>findstr "db2c_DB2" %systemroot%\system32\drivers
\etc\services
db2c_DB2        25000/tcp

C:\Program Files\IBM\SQLLIB\BIN>
 

.::: Archive table 6 months and 24 months SQL Server Using Copy import export write a query to specify the data to transfer :::.

A. Import using ssms
1. create table source

create table pembelian_source(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references customer(KODE_CUSTOMER));

2. create table destination
create table pembelian_dest(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_pembelian,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references customer(KODE_CUSTOMER));