Mas DB2 Notes

Tuesday, November 30, 2021

Installing mongodb

Reference: Install MongoDB Community Edition on Red Hat or CentOS — MongoDB Manual

Configure the package management system (`yum`).

Create a /etc/yum.repos.d/mongodb-org-5.0.repo file so that you can install MongoDB directly using yum:

[mongodb-org-5.0]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/5.0/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-5.0.asc

[root@unicaapdemo yum.repos.d]# yum install -y mongodb-org

MongoDB Repository 14 kB/s | 14 kB 00:00

Dependencies resolved.

======================================================================================================================================================================

Package Architecture Version Repository Size

======================================================================================================================================================================

Installing:

mongodb-org x86_64 5.0.4-1.el8 mongodb-org-5.0 11 k

Installing dependencies:

mongodb-database-tools x86_64 100.5.1-1 mongodb-org-5.0 47 M

mongodb-mongosh x86_64 1.1.4-1.el7 mongodb-org-5.0 43 M

mongodb-org-database x86_64 5.0.4-1.el8 mongodb-org-5.0 11 k

mongodb-org-database-tools-extra x86_64 5.0.4-1.el8 mongodb-org-5.0 16 k

mongodb-org-mongos x86_64 5.0.4-1.el8 mongodb-org-5.0 19 M

mongodb-org-server x86_64 5.0.4-1.el8 mongodb-org-5.0 28 M

mongodb-org-shell x86_64 5.0.4-1.el8 mongodb-org-5.0 15 M

mongodb-org-tools x86_64 5.0.4-1.el8 mongodb-org-5.0 11 k

Transaction Summary

======================================================================================================================================================================

Install 9 Packages

Total download size: 153 M

Installed size: 559 M

Downloading Packages:

(1/9): mongodb-org-5.0.4-1.el8.x86_64.rpm 39 kB/s | 11 kB 00:00

(2/9): mongodb-org-database-5.0.4-1.el8.x86_64.rpm 39 kB/s | 11 kB 00:00

(3/9): mongodb-org-database-tools-extra-5.0.4-1.el8.x86_64.rpm 66 kB/s | 16 kB 00:00

(4/9): mongodb-org-mongos-5.0.4-1.el8.x86_64.rpm 4.7 MB/s | 19 MB 00:04

(5/9): mongodb-mongosh-1.1.4.el8.x86_64.rpm 4.0 MB/s | 43 MB 00:10

(6/9): mongodb-org-server-5.0.4-1.el8.x86_64.rpm 4.5 MB/s | 28 MB 00:06

(7/9): mongodb-org-tools-5.0.4-1.el8.x86_64.rpm 41 kB/s | 11 kB 00:00

(8/9): mongodb-org-shell-5.0.4-1.el8.x86_64.rpm 4.6 MB/s | 15 MB 00:03

(9/9): mongodb-database-tools-100.5.1.x86_64.rpm 3.3 MB/s | 47 MB 00:14

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total 11 MB/s | 153 MB 00:14

warning: /var/cache/dnf/mongodb-org-5.0-85529b2afce6c228/packages/mongodb-database-tools-100.5.1.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID e2c63c11: NOKEY

MongoDB Repository 1.6 kB/s | 1.6 kB 00:00

Importing GPG key 0xE2C63C11:

Userid : "MongoDB 5.0 Release Signing Key <packaging@mongodb.com>"

Fingerprint: F567 9A22 2C64 7C87 527C 2F8C B00A 0BD1 E2C6 3C11

From : https://www.mongodb.org/static/pgp/server-5.0.asc

Key imported successfully

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Installing : mongodb-org-database-tools-extra-5.0.4-1.el8.x86_64 1/9

Installing : mongodb-org-shell-5.0.4-1.el8.x86_64 2/9

Running scriptlet: mongodb-org-server-5.0.4-1.el8.x86_64 3/9

Installing : mongodb-org-server-5.0.4-1.el8.x86_64 3/9

Running scriptlet: mongodb-org-server-5.0.4-1.el8.x86_64 3/9

Created symlink /etc/systemd/system/multi-user.target.wants/mongod.service → /usr/lib/systemd/system/mongod.service.

Installing : mongodb-org-mongos-5.0.4-1.el8.x86_64 4/9

Installing : mongodb-org-database-5.0.4-1.el8.x86_64 5/9

Installing : mongodb-mongosh-1.1.4-1.el7.x86_64 6/9

Running scriptlet: mongodb-database-tools-100.5.1-1.x86_64 7/9

Installing : mongodb-database-tools-100.5.1-1.x86_64 7/9

Running scriptlet: mongodb-database-tools-100.5.1-1.x86_64 7/9

Installing : mongodb-org-tools-5.0.4-1.el8.x86_64 8/9

Installing : mongodb-org-5.0.4-1.el8.x86_64 9/9

Running scriptlet: mongodb-org-5.0.4-1.el8.x86_64 9/9

Verifying : mongodb-database-tools-100.5.1-1.x86_64 1/9

Verifying : mongodb-mongosh-1.1.4-1.el7.x86_64 2/9

Verifying : mongodb-org-5.0.4-1.el8.x86_64 3/9

Verifying : mongodb-org-database-5.0.4-1.el8.x86_64 4/9

Verifying : mongodb-org-database-tools-extra-5.0.4-1.el8.x86_64 5/9

Verifying : mongodb-org-mongos-5.0.4-1.el8.x86_64 6/9

Verifying : mongodb-org-server-5.0.4-1.el8.x86_64 7/9

Verifying : mongodb-org-shell-5.0.4-1.el8.x86_64 8/9

Verifying : mongodb-org-tools-5.0.4-1.el8.x86_64 9/9

Installed products updated.

Installed:

mongodb-database-tools-100.5.1-1.x86_64 mongodb-mongosh-1.1.4-1.el7.x86_64 mongodb-org-5.0.4-1.el8.x86_64

mongodb-org-database-5.0.4-1.el8.x86_64 mongodb-org-database-tools-extra-5.0.4-1.el8.x86_64 mongodb-org-mongos-5.0.4-1.el8.x86_64

mongodb-org-server-5.0.4-1.el8.x86_64 mongodb-org-shell-5.0.4-1.el8.x86_64 mongodb-org-tools-5.0.4-1.el8.x86_64

Complete!

[root@unicaapdemo yum.repos.d]# vi /etc/mongod.conf

[root@unicaapdemo mongodb]# sudo semanage fcontext -a -t mongod_var_lib_t '/apps/mongodb/data'

[root@unicaapdemo mongodb]# sudo chcon -Rv -u system_u -t mongod_var_lib_t '/apps/mongodb/data'

changing security context of '/apps/mongodb/data'

[root@unicaapdemo mongodb]# sudo restorecon -R -v '/apps/mongodb/data'

[root@unicaapdemo mongodb]#

[root@unicaapdemo mongodb]# sudo semanage fcontext -a -t mongod_log_t '/apps/mongodb/logs'

[root@unicaapdemo mongodb]# sudo chcon -Rv -u system_u -t mongod_log_t '/apps/mongodb/logs'

changing security context of '/apps/mongodb/logs'

[root@unicaapdemo mongodb]# sudo restorecon -R -v '/apps/mongodb/logs'

[root@unicaapdemo mongodb]# ls -ld /apps/mongodb/logs

drwxr-xr-x. 2 mongod mongod 6 Dec 1 14:11 /apps/mongodb/logs

[root@unicaapdemo mongodb]# ls -ld /apps/mongodb/data

[root@unicaapdemo mongodb]# cat /etc/mongod.conf

# mongod.conf

# for documentation of all options, see:

# http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.

systemLog:

destination: file

logAppend: true

path: /apps/mongodb/logs/mongod.log

# Where and how to store data.

storage:

dbPath: /apps/mongodb/data

journal:

enabled: true

# engine:

# wiredTiger:

[root@unicaapdemo mongodb]# sudo systemctl start mongod

[root@unicaapdemo mongodb]# systemctl status mongod

● mongod.service - MongoDB Database Server

Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)

Active: active (running) since Wed 2021-12-01 14:18:19 +08; 20s ago

Docs: https://docs.mongodb.org/manual

Process: 140583 ExecStart=/usr/bin/mongod $OPTIONS (code=exited, status=0/SUCCESS)

Process: 140580 ExecStartPre=/usr/bin/chmod 0755 /var/run/mongodb (code=exited, status=0/SUCCESS)

Process: 140578 ExecStartPre=/usr/bin/chown mongod:mongod /var/run/mongodb (code=exited, status=0/SUCCESS)

Process: 140576 ExecStartPre=/usr/bin/mkdir -p /var/run/mongodb (code=exited, status=0/SUCCESS)

Main PID: 140585 (mongod)

Memory: 65.4M

CGroup: /system.slice/mongod.service

└─140585 /usr/bin/mongod -f /etc/mongod.conf

Dec 01 14:18:19 unicaapdemo systemd[1]: Starting MongoDB Database Server...

Dec 01 14:18:19 unicaapdemo mongod[140583]: about to fork child process, waiting until server is ready for connections.

Dec 01 14:18:19 unicaapdemo mongod[140583]: forked process: 140585

Dec 01 14:18:19 unicaapdemo mongod[140583]: child process started successfully, parent exiting

Dec 01 14:18:19 unicaapdemo systemd[1]: Started MongoDB Database Server.

[root@unicaapdemo mongodb]# mongo

MongoDB shell version v5.0.4

connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb

Implicit session: session { "id" : UUID("7ca50204-948d-4836-8447-9db93d22df09") }

MongoDB server version: 5.0.4

================

Warning: the "mongo" shell has been superseded by "mongosh",

which delivers improved usability and compatibility.The "mongo" shell has been deprecated and will be removed in

an upcoming release.

For installation instructions, see

https://docs.mongodb.com/mongodb-shell/install/

================

Welcome to the MongoDB shell.

For interactive help, type "help".

For more comprehensive documentation, see

https://docs.mongodb.com/

Questions? Try the MongoDB Developer Community Forums

https://community.mongodb.com

---

The server generated these startup warnings when booting:

2021-12-01T14:18:19.627+08:00: Access control is not enabled for the database. Read and write access to data and configuration is unrestricted

2021-12-01T14:18:19.627+08:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'

---

Enable MongoDB's free cloud-based monitoring service, which will then receive and display

metrics about your deployment (disk utilization, CPU, operation statistics, etc).

The monitoring data will be available on a MongoDB website with a unique URL accessible to you

and anyone you share the URL with. MongoDB may use this information to make product

improvements and to suggest MongoDB products and deployment options to you.

To enable free monitoring, run the following command: db.enableFreeMonitoring()

To permanently disable this reminder, run the following command: db.disableFreeMonitoring()

---

> show dbs

admin 0.000GB

config 0.000GB

local 0.000GB

Sunday, November 28, 2021

mariadb truncate table with foreign constraints

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE usm_configuration;

SET FOREIGN_KEY_CHECKS = 1;

OneDB pre-requisites

Need to install the following packages:

yum install libncurses*

Thursday, November 25, 2021

Migrate SQLServer Windows to Unix

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-restore-database?view=sql-server-ver15

https://www.sqlshack.com/move-sql-database-files-in-sql-server-2019-on-linux/

https://www.mssqltips.com/sqlservertip/5346/migrate-sql-server-database-from-windows-to-sql-server-on-linux-in-cloud-part-3/

[root@database-rhel84 data]# mkdir mssql-data

[root@database-rhel84 data]# ls -l

total 1260160

-rw-r--r--. 1 root root 1290392111 Nov 22 18:16 dmdb.sql.gz

drwxr-xr-x. 2 root root 6 Nov 26 06:40 mssql-data

drwxr-xr-x. 3 root root 4096 Nov 24 14:07 mssqldbbackup

drwxr-xr-x. 13 mysql mysql 4096 Nov 26 01:52 mysql-data

[root@database-rhel84 mssqldbbackup]# sqlcmd -S localhost -U sa

Password:

1> RESTORE FILELISTONLY FROM DISK = '/data/mssqldbbackup/dcc.bak'

2> go

LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint SnapshotUrl

-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------- --------------------------- ------------------------------------ --------------------------- --------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------- ------------------------------------ ---------- --------- ------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

demoCombined_Data C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dcc.mdf D PRIMARY 2242314240 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 2062352384 512 1 NULL 106335000000033000037 C81F6BD0-1B95-496B-9CEE-7F2CE95D86DC 0 1 NULL NULL

demoCombined_Log C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dcc.LDF L NULL 786432 2199023255552 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL NULL

(2 rows affected)

root@database-rhel84 data]# sqlcmd -S localhost -U sa

Password:

1> SELECT Name FROM sys.Databases

2> go

Name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

dcc

(5 rows affected)

Tuesday, November 23, 2021

Increase mariadb max connections

[root@database-rhel84 my.cnf.d]# vi server.cnf

[root@database-rhel84 my.cnf.d]# pwd

/etc/my.cnf.d

# this is only for the mysqld standalone daemon

[mysqld]

datadir=/data/mysql-data

socket=/data/mysql-data/mysql.sock

innodb-page-size=32768

character-set-server=utf8

sql_mode='NO_BACKSLASH_ESCAPES'

innodb_strict_mode=0

lower_case_table_names = 1

max_connections=500

[root@database-rhel84 my.cnf.d]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 3

Server version: 10.5.13-MariaDB MariaDB Server

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

MariaDB [(none)]> SET GLOBAL max_connections=500

-> ;

Query OK, 0 rows affected (0.000 sec)

You can do the following to check:

MariaDB [(none)]> show variables

Monday, November 22, 2021

Moving mariadb database

https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb

[root@database-rhel79 dmdbmariadbbackup]# mysqldump -p dmdb > dmdb.sql

Enter password:

[root@database-rhel79 dmdbmariadbbackup]# head dmdb.sql

-- MariaDB dump 10.19 Distrib 10.5.9-MariaDB, for Linux (x86_64)

-- Host: localhost Database: dmdb

-- ------------------------------------------------------

-- Server version 10.4.21-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

[root@database-rhel84 data]# mysql -p dmdb < dmdb.sql

Enter password:

[root@database-rhel84 data]#

Friday, November 19, 2021

Setting up mariaDB OCBC on redhat

1. install unixODBC - yum install unixODBC

2. install mariadb ODBC

[root@unicaapdemo bin]# yum install mariadb-connector-odbc

Last metadata expiration check: 16:31:52 ago on Fri 19 Nov 2021 10:00:09 PM +08.

Dependencies resolved.

=============================================================================================================================================================================

Package Architecture Version Repository Size

=============================================================================================================================================================================

Installing:

mariadb-connector-odbc x86_64 3.0.7-1.el8 LocalRepo_AppStream 113 k

Transaction Summary

=============================================================================================================================================================================

Install 1 Package

Total size: 113 k

Installed size: 351 k

Is this ok [y/N]: y

Downloading Packages:

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Installing : mariadb-connector-odbc-3.0.7-1.el8.x86_64 1/1

Running scriptlet: mariadb-connector-odbc-3.0.7-1.el8.x86_64 1/1

Verifying : mariadb-connector-odbc-3.0.7-1.el8.x86_64 1/1

Installed products updated.

Installed:

mariadb-connector-odbc-3.0.7-1.el8.x86_64

Complete!

3. Add entry in /etc/odbcinst.ini

[MariaDB]

Description=ODBC for MariaDB

Driver=/usr/lib64/libmaodbc.so

Driver64=/usr/lib64/libmaodbc.so

FileUsage=1

4 Add entry in /etc/odbc.ini (match odbcinst.ini)

[ucdb]

Driver=MariaDB

User=unica

Password=unica@987!

Database=ucdb

Server=192.168.1.89

Port=3306

5. Test connection:

[root@unicaapdemo bin]# isql ucdb

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

SQL>

odbcinst -j

[root@unicaapdemo mariadb]# odbcinst -j

unixODBC 2.3.7

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /etc/odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

Tuesday, November 30, 2021

Configure the package management system (yum).

Sunday, November 28, 2021

Thursday, November 25, 2021

Tuesday, November 23, 2021

Monday, November 22, 2021

Friday, November 19, 2021

Configure the package management system (`yum`).