数据库接口及中间件

学习内容关键字：

odbc、SQLAlchemy、python marshal、Pandas、中间件、连接池、磁盘调度、LVM管理

1. odbc操作流程

1.1 安装ODBC环境（Linux）

    编辑/etc/odbcinst.ini

        [DM8 ODBC DRIVER]

        Description = ODBC DRIVER FOR DM8

        Driver = /lib/libdodbc.so

    编辑/etc/odbc.ini

        [dm]

        Description = DM ODBC DSN

        Driver = DM8 ODBC DRIVER

        SERVER = localhost

        UID = SYSDBA

        PWD = SYSDBA

        TCP_PORT = 5236

1.2 连接到数据源

    SQLAllocHandle         --分配环境、连接、语句或描述符句柄

    SQLConnect               --建立和驱动程序或者数据源的连接  

    SQLDriverConnect     --支持比SQLConnect更多的数据源连接信息

    SQLBrowseConnect   --支持交互方法检索或列出连接数据源所需要的属性或值

1.3 获取驱动程序和数据源信息

    SQLDataSources        --能被多次调用获取应用程序使用的所有数据源的名字

    SQLDrivers                 --返回所有安装过的驱动程序清单

    SQLGetInfo                --返回连接的驱动程序和数据源的元信息

    SQLGetFunctions       --返回指定的驱动程序是否支持某个特定函数的信息

    SQLGetTypeInfo        --返回指定的数据源支持的数据类型信息

1.4 设置或者获取驱动程序属性

    SQLSetConnectAttr    --设置连接属性

    SQLGetConnectAttr    --返回连接属性

    SQLSetEnvAttr            --设置环境属性值

    SQLGetEnvAttr            --返回环境属性值

    SQLSetStmtAttr           --设置语句属性值

    SQLGetStmAttr            --返回语句属性值

1.5 设置或者获取描述符字段

    SQLGetDescField        --返回单个描述符字段的值

    SQLGetDescRec          --返回当前描述符记录的多个字段的值

    SQLSetDescField         --设置单个描述符字典的值

    SQLSetDescRec           --设置描述符记录的多个字段

1.6 准备SQL语句

    SQLPrepare                --准备要执行的SQL语句

    SQLBindParameter     --在SQL语句中分配参数的缓冲区

    SQLGetCursorName   --返回语句句柄相关的游标名称

    SQLSetCursorName    --设置与语句句柄相关的游标名称

    SQLSetScrollOptions   --设置控制游标行为的选项

1.7 提交SQL请求

    SQLExecute                --执行准备好的SQL

    SQLExecDirect            --执行一条SQL

    SQLNativeSql             --返回驱动对一条SQL的翻译

    SQLDescribeParam    --返回对SQL语句中指定参数的描述

    SQLNumParamData   --返回SQL语句中参数的个数

    SQLParamData           --与SQLPutData联合使用在运行时给参数赋值

    SQLPutData                --SQL语句运行时给部分或全部参数赋值

1.8 检索结果集和相关信息

    SQLRowCount            --返回INSERT、UPDATE或DELETE等语句影响的行数

    SQLNumResultCols    --返回结果集中列的数据

    SQLDescribeCol          --返货结果集中列的描述符记录

    SQLColAttribute         --返回结果集中列的属性

    SQLBindCol                 --为结果集中的列分配缓冲区

    SQLFetch                    --在结果集中检索下一行元组

    SQLFetchScroll            --返回指定的结果行

    SQLGetData                --返货结果集中当前行某一列的值

    SQLSetPos                   --取到的数据集中设置游标的位置，记录集中的数据能刷新、更新或删除

    SQLBulkOperations    --执行块插入和块书签操作，包括根据书签更新、删除或取数据

    SQLMoreResults         --确定是否能够获得更多的结果集，若能就执行下一个结果集的初始化操作

    SQLGetDiagField        --返回一个字段值或者一个诊断数据记录

    SQLGetDiagRec           --返回多个字段值或者一个诊断数据记录

1.9 取得数据源系统表信息

    SQLColumnPrivileges    --返回一个关于指定表的列的列表以及相关的权限信息；

    SQLColumns                  --返回指定表的列信息的列表；

    SQLForeignKeys            --返回指定表的外键信息的列表；

    SQLPrimaryKeys            --返回指定表的主键信息的列表；

    SQLProcedureColumns --返回指定存储过程的参数信息的列表；

    SQLProcedures              --返回指定数据源的存储过程信息的列表；

    SQLSpecialColumns       --返回唯一确定某一行的列的信息，或者当某一事务修改一

行的时候自动更新各列的信息；

    SQLStatistics                  --返回一个单表的相关统计信息和索引信息；

    SQLTablePrivileges        --返回相关各表的名称以及相关的权限信息；

    SQLTables                      --返回指定数据源中表信息。

1.10 终止语句执行

    SQLFreeStmt：终止语句执行，关闭所有相关的游标，放弃没有提交的结果，选择

释放与指定语句句柄相关的资源；

    SQLCloseCursor：关闭一个打开的游标，放弃没有提交的结果；

    SQLCancel：放弃执行一条 SQL 语句；

    SQLEndTran：提交或者回滚事务。

1.11 中断连接

    SQLDisconnect：关闭指定连接；

    SQLFreeHandle：释放环境、连接、语句或者描述符句柄。

1. 由python提示参数数目不对引发python接口及传参的探究

问题描述：此函数需要7个参数，而示例提供了8个参数

相关报错实例一：

相关报错示例二：

根据报错信息整理出可能涉及到的相关函数包括

2.1 SF_GET_SCHEMA_ID_BY_NAME()和SF_GET_SCHEMA_NAME_BY_ID()

2.2 construct_params()

python marshal 对象序列化和反序列化相关Python param.marshal_param函数中的的具体实现规则，通过分析函数实现发现是由于python类默认有一个self参数，当多传入参数时会抛出设置好的异常提示参数多于设置值

2.3 Pandas DataFrame的理解

过程应用举例：

——创建一个内存中的 SQLite 数据库。

from sqlalchemy import create_engineimport pandas as pd

engine = create_engine('sqlite://', echo=False)

——从 0 创建一个总共有 3行的表。

df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})

——将 DataFrame 写入数据库

df.to_sql('users', con=engine)

——从数据库中提取数据

engine.execute("SELECT * FROM users").fetchall()

——也可以将 sqlalchemy.engine.Connection 传递给 con：

         with engine.begin() as connection:

            df1 = pd.DataFrame({'name' : ['User 4', 'User 5']})

            df1.to_sql('users', con=connection, if_exists='append')

2.4 Pandas to_sql()函数方法

2.4.1 特点

把储存在 DataFrame 里面的记录写到 SQL 数据库中；

可以支持所有被 SQLAlchemy 支持的数据库类型；

在写入到 SQL 数据库中的过程中，可以新建表，append 到表，以及覆盖表。

2.4.2 语法

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, Chunksize=None, dtype=None, method=None)

2.4.3 参数说明

name：str 类型，表示 SQL 表的名称。

con：sqlalchemy.engine.(Engine 或者 Connection) 类型 或者 sqlite3.Connection 类型。

——使用 SQLAlchemy 使得我们可以使用该库支持的所有数据库。该方法也提供对 sqlite3.Connection 对象的支持，该方法已过时但因使用范围广而难以替代所以才保留了这个支持。用户应对 SQLAlchemy connectable 的引擎处理和连接关闭负责。详见这里。

schema：str 类型，可选的。

——具体说明 schema（如果数据库支持这个）。如果为 None，则将使用默认 schema。

if_exists：{'fail', 'repalce', 'append'}，默认值为 'fail'。

——该参数指定如果一个表已经存在的情况：

fail：Raise 一个 ValueError。

replace：在插入新的值之前 drop 这个表。

append：在现寸的表里面插入新的值。

index：bool 类型，默认值为 True。

——将 DataFrame 的 index 写为一列。将使用 index_label 作为表中的 column name。

index_label：str 类型或者 sequence 类型，默认值为 None。

——为 index column(s) 提供 column 的 label。如果本参数被设置为 None，同时前一个参数 index 被设置为 True，那么该 index 的 names 将会被使用做由 index 转化而来 的 column(s) 的名字。如果你的 DataFrame 使用了 MultiIndex，则应当提供一个 sequence。

chunksize：int 类型，可选的。

——具体说明一次写入多少行的数据。默认情况下，所有的行会被一次写入。

dtype：dict 类型或者 scalar 类型，可选的。

——具体说明 columns 的 datatype。如果使用字典类型，keys 应当是 columns names，values 应当是 SQLAlchemy 类型，或者字符串型（针对于 sqplite3 legacy mode 的情况）。如果我们提供了一个 scalar 类型，那么这个值会被应用到所有的 columns。

method：{None, 'multi', callable}，可选的。

——控制 SQL 插入语句的使用：

None：使用标准的 SQL INSERT 语句（每行一个）

'multi'：在一个 INSERT 语句中传递多个值

带有签名的 callable (pd_table, conn, keys, data_iter)

2.4.4 返回值

none或int型:被 to_sql 影响到的行的数量，如果传递给 callable 的 method 没有返回整数的行数，则 None 会被返回。

1. jdbc相关总结

jdbc要使用try()catch()，及时关闭申请资源避免内存泄漏。

hibernate是一个Java ORM框架，实现Java对象（POJO）与数据库表之间的映射

需要加载DM JDBC驱动和DM方言包并配置DM连接信息，其中：

DM驱动和方言包

驱动名称    DmJdbcDriver18.jar

驱动位置    dmdbms/drivers/jdbc/

方言名称    DmDialect-for-hibernate3.0.jar

方言位置    dmdbms/drivers/jdbc/dialect/

注意：方言包要与hibernate版本匹配，同时对JDK版本有要求，如DmDialect-for-hibernate5.0.jar对应Jdk1.7及以上，hibernate5.0 -- 5.2 环境参见方言目录下的readme.txt

jdbc接口连接步骤简述：

创建环境→建立连接→申请句柄执行命令（设置参数、获取结果）→释放句柄→释放连接

jdbc连接基础参数：

jdbc.driver=dm.jdbc.driver.DmDriver

jdbc.url=jdbc:dm://localhost:5236

jdbc.username=test

jdbc.password=Test12345

小技巧，字符串可以拆分拼接成以下格式便于维护：

StringBuffer sql = new StringBuffer();

sql.append(“ insert into t_user( \n”);

sql.append(“ id, name, phone, email \n “);

sql.append(“ )values ( \n”);

sql.append(“ ?, ?, ?, ? \n”);

sql.append(“ ) “);

1. 中间件及内存池相关总结

概述：分析熟悉常用中间件的擅长领域及连接方法，应对不同场景及要求下数据库与中间件的适配连接

常用中间件：Weblogic（Oracle）、WebSphere（IBM）、Tomcat（开源）、Apusic（金蝶）、TongWeb（东方通）、SAP（SAP）、IIS（微软）

1. 磁盘调度及LVM逻辑卷管理

5.1 磁盘调度

三种调度方式

    cfq/dedline（适合数据库）/noop（适合固态）

查看磁盘调度方式

cat /sys/block/sd*/queue/scheduler

临时修改磁盘IO调度器

echo 'cfq'>/sys/block/sd*/queue/scneduler

永久修改IO调度器

vi /boot/grub/menu.lst

修改内核引导参数，加入elevator=调度程序名

5.2 LVM逻辑卷管理，动态管理磁盘

PV物理卷+VG基于物理卷+LV基于卷组

扩容操作：

--pvcreate 物理卷全路径名

pvcreate /dev/sdb

--vgcreate卷组名 物理卷全路径名

vgcreate vg_data /dev/sdb

--lvcreate -l 大小 -n 创建逻辑卷 卷组名

lvcreate -l 100%VG -n lv_data vg_data

--1.选择分区创建为PV

pvcreate 物理卷全路径名

--2.选择PV加入到VG

vgextend vg卷组名 物理卷全路径名

--3.扩容LV

lvextend -L +大小 lv逻辑卷

--4.增加文件系统容量

resize2fs lv逻辑卷

1. 本周遇到的问题及解决方式

1. 网络通信异常（做好充分的上线检查可以避免很多不必要的麻烦）

——句柄问题

ulimit -n 65535必要性（必须重启数据库才能生效）

——内存资源显示导致连接无法创建

cat /proc/sys/vm/overcommit_memory=1（0/1无限制/2有比例上限）

cat /proc/sys/vm/overcommit_ratio=50（memory=2时设置ratio比例）

MAX_SESSION限制：ps -ef|grep dm.ini

netstat -apn|grep dmserver|grep ESTABLISHED|awk'{print $5}'|awk -F":"'{print $1}'|sort -nr |uniq -c |sort -nr

（MAX_SESSION最大连接数是设置上限数+1）

——LISTEN_IP限制：（空串|手动|和PORT_NUM配合使用监听IP地址）

——错误码/errno：（存储linux系统调用过程中发生的错误）

cat /usr/include/asm-generic/errno-base.h

cat /usr/include/asm-generic/errno.h

        2. 回滚记录版本太旧

——调小UNDO_RETENTION，加快PURGE，导致事务开始后对应的查询记录在其它事务发生修改，且修改后超过UNDO_RETENTION时间，本事务才执行结束就会报错

SP_SET_PARA_DOUBULE_VALUE(1,'UNDO_RETENTION',1);

——具体解决方法

SF_SET_SESSION_PARA_VALUE('ENABLE_IGNORE_PURGE_REC',1);

开启忽略回滚段记录

0报错，1忽略这一条记录继续执行

查看哪些事务产生的待PURGE记录

SELECT * FROM v$purge/v$purge_pseg_obj/v$purge_pseg_tab

        3. 问题排查方向、排查手段

——硬件三大件（内存、CPU、磁盘IO）：借助nmon、DEM...监控指标横纵向对比

——让数据库变透明：数据库运行日志、dmsql日志、各类系统视图、检查点刷新规律、归档切换频率等...

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台