更新日期：2024年09月11日
取值：[0|1]
默认值：1
说明：用于单表等值hash 查询条件的优化，启用该参数则当单表包含hash 列的等
GBase 8a MPP Cluster 参数手册
文档版本2022-06-07
南大通用数据技术股份有限公司
48
值条件时，
进行hash 优化，
sql 语句仅仅发送给单个节点。
默认为1(0 - OFF, 1 - ON)。
如：
select * from bas.clcinfdta where 1=1 and clt_nbr=‘7319022720’ limit 30 offset 0
不能查到记录，去掉1=1 才可正确查询，
可设置gcluster_single_hash_node_optimize=0，临时关闭
修改方式：
可使用set 语句修改值也可在配置文件中修改值。
适用于session、
global
范围均可。
create_clob 演示如何对智能大对象执行下列任务：
以用户定义的存储特征来创建智能大对象。
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 961 -
将新的智能大对象插入至数据库列内。
示例的存储特征
create_clob 程序创建 advert_descr 智能大对象，其有下列用户定义的存储特征：
开启日志记录：LO_LOG
保持最后的访问时间（缺省来自 advert_descr 列）：LO_KEEP_ACCESSTIME
完整性高
分配 extent 大小为 10 KB
EXEC SQL include int8;
EXEC SQL include locator;
EXEC SQL define BUFSZ 10;
extern char statement[80];
main()
{
EXEC SQL BEGIN DECLARE SECTION;
int8 catalog_num, estbytes, offset;
int error, numbytes, lofd, ic_num, buflen = 256;
char buf[256], srvr_name[256], col_name[300];
ifx_lo_create_spec_t *create_spec;
fixed binary 'clob' ifx_lo_t descr;
EXEC SQL END DECLARE SECTION;
void nullterm(char *);
void handle_lo_error(int);
EXEC SQL whenever sqlerror call whenexp_chk;
EXEC SQL whenever sqlwarning call whenexp_chk;
printf("CREATE_CLOB Sample ESQL program running.\n\n");
strcpy(statement, "CONNECT stmt");
EXEC SQL connect to 'stores7';
EXEC SQL get diagnostics exception 1
:srvr_name = server_name;
nullterm(srvr_name);
/* Allocate and initialize the LO-specification structure */
error = ifx_lo_def_create_spec(&create_spec);
if (error < 0)
{
strcpy(statement, "ifx_lo_def_create_spec()");
handle_lo_error(error);
}
/* Get the column-level storage characteristics for the
* CLOB column, advert_descr.
*/
sprintf(col_name, "stores7@%s:catalog.advert_descr",
srvr_name);
error = ifx_lo_col_info(col_name, create_spec);
if (error < 0)
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 962 -
{
strcpy(statement, "ifx_lo_col_info()");
handle_lo_error(error);
}
/* Override column-level storage characteristics for
* advert_desc with the following user-defined storage
* characteristics:
* no logging
* extent size = 10 kilobytes
*/
ifx_lo_specset_flags(create_spec,LO_LOG);
ifx_int8cvint(BUFSZ, &estbytes);
ifx_lo_specset_estbytes(create_spec, &estbytes);
/* Create an LO-specification structure for the smart large object */
if ((lofd = ifx_lo_create(create_spec, LO_RDWR,
&descr, &error)) == -1)
{
strcpy(statement, "ifx_lo_create()");
handle_lo_error(error);
}
/* Copy data into the character buffer 'buf' */
sprintf(buf, "%s %s",
"Pro model infielder's glove. Highest quality leather and
stitching. "
"Long-fingered, deep pocket, generous web.");
/* Write contents of character buffer to the open smart
* large object that lofd points to. */
ifx_int8cvint(0, &offset);
numbytes = ifx_lo_writewithseek(lofd, buf, buflen,
&offset, LO_SEEK_SET, &error);
if ( numbytes < buflen )
{
strcpy(statement, "ifx_lo_writewithseek()");
handle_lo_error(error);
}
/* Insert the smart large object into the table */
strcpy(statement, "INSERT INTO catalog");
EXEC SQL insert into catalog values (0, 1, 'HSK', 'case', ROW(NULL,
NULL),:descr);
/* Need code to find out what the catalog_num value was
* assigned to new row */
/* Close the LO file descriptor */
ifx_lo_close(lofd);
/* Select back the newly inserted value. The SELECT
* returns an LO-pointer structure, which you then use to
* open a smart large object to get an LO file descriptor.
*/
ifx_getserial8(&catalog_num);
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 963 -
strcpy(statement, "SELECT FROM catalog");
EXEC SQL select advert_descr into :descr from catalog
where catalog_num = :catalog_num;
/* Use the returned LO-pointer structure to open a smart
* large object and get an LO file descriptor.
*/
lofd = ifx_lo_open(&descr, LO_RDONLY, &error);
if (error < 0)
{
strcpy(statement, "ifx_lo_open()");
handle_lo_error(error);
}
/* Use the LO file descriptor to read the data in the
* smart large object.
*/
ifx_int8cvint(0, &offset);
strcpy(buf, "");
numbytes = ifx_lo_readwithseek(lofd, buf, buflen,
&offset, LO_SEEK_CUR, &error);
if (error || numbytes == 0)
{
strcpy(statement, "ifx_lo_readwithseek()");
handle_lo_error(error);
}
if(ifx_int8soint(&catalog_num, _num) != 0)
printf("\nifx_int8soint failed to convert catalog_num to int");
printf("\nContents of column \'descr\' for catalog_num:
%d \n\t%s\n",
ic_num, buf);
/* Close open smart large object */
ifx_lo_close(lofd);
/* Free LO-specification structure */
ifx_lo_spec_free(create_spec);
}
void handle_lo_error(error_num)
int error_num;
{
printf("%s generated error %d\n", statement, error_num);
exit(1);
}
void nullterm(str)
char *str;
{
char *end;
end = str + 256;
while(*str != ' ' && *str != '\0' && str < end)
{
++str;
}
if(str >= end)
printf("Error: end of str reached\n");
if(*str == ' ')
*str = '\0';
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 964 -
}
/* Include source code for whenexp_chk() exception-checking
* routine
*/
EXEC SQL include exp_chk.ec;
get_lo_info.ec 程序
此程序检索关于存储在 BLOB 列中的智能大对象的信息。
#include
EXEC SQL define BUFSZ 10;
extern char statement[80];
main()
{
int error, ic_num, oflags, cflags, extsz, imsize, isize, iebytes;
time_t time;
struct tm *date_time;
char col_name[300], sbspc[129];
EXEC SQL BEGIN DECLARE SECTION;
fixed binary 'blob' ifx_lo_t picture;
char srvr_name[256];
ifx_lo_create_spec_t *cspec;
ifx_lo_stat_t *stats;
ifx_int8_t size, c_num, estbytes, maxsize;
int lofd;
long atime, ctime, mtime, refcnt;
EXEC SQL END DECLARE SECTION;
void nullterm(char *);
void handle_lo_error(int);
imsize = isize = iebytes = 0;
EXEC SQL whenever sqlerror call whenexp_chk;
EXEC SQL whenever sqlwarning call whenexp_chk;
printf("GET_LO_INFO Sample ESQL program running.\n\n");
strcpy(statement, "CONNECT stmt");
EXEC SQL connect to 'stores7';
EXEC SQL get diagnostics exception 1
:srvr_name = server_name;
nullterm(srvr_name);
EXEC SQL declare ifxcursor cursor for
select catalog_num, advert.picture
into :c_num, :picture
from catalog
where advert.picture is not null;
EXEC SQL open ifxcursor;
while(1)
{
EXEC SQL fetch ifxcursor;
if (strncmp(SQLSTATE, "00", 2) != 0)
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 965 -
{
if(strncmp(SQLSTATE, "02", 2) != 0)
printf("SQLSTATE after fetch is %s\n", SQLSTATE);
break;
}
/* Use the returned LO-pointer structure to open a smart
* large object and get an LO file descriptor.
*/
lofd = ifx_lo_open(&picture, LO_RDONLY, &error);
if (error < 0)
{
strcpy(statement, "ifx_lo_open()");
handle_lo_error(error);
}
if(ifx_lo_stat(lofd, &stats) < 0)
{
printf("\nifx_lo_stat() < 0");
break;
}
if(ifx_int8soint(&c_num, _num) != 0)
ic_num = 99999;
if((ifx_lo_stat_size(stats, &size)) < 0)
isize = 0;
else
if(ifx_int8soint(&size, &isize) != 0)
{
printf("\nFailed to convert size");
isize = 0;
}
if((refcnt = ifx_lo_stat_refcnt(stats)) < 0)
refcnt = 0;
printf("\n\nCatalog number %d", ic_num);
printf("\nSize is %d, reference count is %d", isize, refcnt);
if((atime = ifx_lo_stat_atime(stats)) < 0)
printf("\nNo atime available");
else
{
time = (time_t)atime;
date_time = localtime(&time);
printf("\nTime of last access: %s", asctime(date_time));
}
if((ctime = ifx_lo_stat_ctime(stats)) < 0)
printf("\nNo ctime available");
else
{
time = (time_t)ctime;
date_time = localtime(&time);
printf("Time of last change: %s", asctime(date_time));
}
if((mtime = ifx_lo_stat_mtime_sec(stats)) < 0)
printf("\nNo mtime available");
else
{
time = (time_t)mtime;
date_time = localtime(&time);
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 966 -
printf("Time to the second of last modification: %s",
asctime(date_time));
}
if((cspec = ifx_lo_stat_cspec(stats)) == NULL)
{
printf("\nUnable to access ifx_lo_create_spec_t structure");
break;
}
oflags = ifx_lo_specget_def_open_flags(cspec);
printf("\nDefault open flags are: %d", oflags);
if(ifx_lo_specget_estbytes(cspec, &estbytes) == -1)
{
printf("\nifx_lo_specget_estbytes() failed");
break;
}
if(ifx_int8soint(&estbytes, &iebytes) != 0)
{
printf("\nFailed to convert estimated bytes");
}
printf("\nEstimated size of smart LO is: %d", iebytes);
if((extsz = ifx_lo_specget_extsz(cspec)) == -1)
{
printf("\nifx_lo_specget_extsz() failed");
break;
}
printf("\nAllocation extent size of smart LO is: %d", extsz);
if((cflags = ifx_lo_specget_flags(cspec)) == -1)
{
printf("\nifx_lo_specget_flags() failed");
break;
}
printf("\nCreate-time flags of smart LO are: %d", cflags);
if(ifx_lo_specget_maxbytes(cspec, &maxsize) == -1)
{
printf("\nifx_lo_specget_maxsize() failed");
break;
}
if(ifx_int8soint(&maxsize, &imsize) != 0)
{
printf("\nFailed to convert maximum size");
break;
}
if(imsize == -1)
printf("\nMaximum size of smart LO is: No limit");
else
printf("\nMaximum size of smart LO is: %d\n", imsize);
if(ifx_lo_specget_sbspace(cspec, sbspc, sizeof(sbspc)) == -1)
printf("\nFailed to obtain sbspace name");
else
printf("\nSbspace name is %s\n", sbspc);
}
/* Close smart large object */
ifx_lo_close(lofd);
ifx_lo_stat_free(stats);
EXEC SQL close ifxcursor;
EXEC SQL free ifxcursor;
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 967 -
}
void handle_lo_error(error_num)
int error_num;
{
printf("%s generated error %d\n", statement, error_num);
exit(1);
}
void nullterm(str)
char *str;
{
char *end;
end = str + 256;
while(*str != ' ' && *str != '\0' && str < end)
{
++str;
}
if(str >= end)
printf("Error: end of str reached\n");
if(*str == ' ')
*str = '\0';
}
/* Include source code for whenexp_chk() exception-checking
* routine
*/
EXEC SQL include exp_chk.ec;
HIVE 引擎的分区表，在INSERT 时指定各分区字段的名字和值。
示例：静态分区INSERT
gbase> DROP TABLE IF EXISTS h3;
Query OK, 0 rows affected
gbase> CREATE TABLE h3(a INT, b INT) PARTITIONED BY (c INT, d INT) ENGINE='HIVE';
Query OK, 0 rows affected
gbase> INSERT INTO h3 PARTITION(c=100, d=100) values(2, 2);
Query OK, 1 row affected
GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 757
gbase> INSERT INTO h3 PARTITION(c=100, d=200) values(1, 1);
Query OK, 1 row affected
gbase> SELECT * FROM h3;
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 2 | 2 | 100 | 100 |
| 1 | 1 | 100 | 200 |
+------+------+------+------+
2 rows in set