Informix Client SDK support position

Informix Client SDK support position

http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&appname=iSource&supplier=877&letternum=ENUSZP15-0265

Advertisements

Interesting SQL Server/Windows 10 Articles today!

MCP Insider

https://borntolearn.mslearn.net/forums/f/608/t/326726

Understanding how SQL Server executes a query

http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/

Windows 10 Technical Preview Build 10049 now available, includes new browser Project Spartan!

http://blogs.windows.com/bloggingwindows/2015/03/30/windows-10-technical-preview-build-10049-now-available/


DB2 licensing checks

DB2 License compliance report returns a VIOLATION status against a certain feature

http://www-01.ibm.com/support/docview.wss?uid=swg21700656&myns=swgimgmt&mynp=OCSSEPGG&mync=E&cm_sp=swgimgmt-_-OCSSEPGG-_-E

Run license compliant report

db2licm -g /tmp/report.out ; cat /tmp/report.out

LIC1440I License compliance report generated successfully.

License Compliance Report
DB2 Enterprise Server Edition In compliance

DB2 Database Partitioning: “Not used”
DB2 Performance Optimization ESE: “Not used”
DB2 Storage Optimization: “Violation”
DB2 Advanced Access Control: “Not used”
DB2 Geodetic Data Management: “Not used”
IBM Homogeneous Replication ESE: “Not used”

So the DB2 Storage Optimization feature is in “VIOLATION”

Check if violation item is licensed

db2licm -l
Product name: “DB2 Enterprise Server Edition”
License type: “CPU Option”
Expiry date: “Permanent”
Product identifier: “db2ese”
Version information: “9.7”
Enforcement policy: “Soft Stop”
Features:
DB2 Performance Optimization ESE: “Not licensed”
DB2 Storage Optimization: “Not licensed”
DB2 Advanced Access Control: “Not licensed”
DB2 Geodetic Data Management: “Not licensed”
IBM Homogeneous Replication ESE: “Not licensed”

It would mean that the license for the feature in “Violation” has not been applied.

Run queries to check what is using the features in violation

For the DB2 Advanced Access Control Feature:

Check for tables that use label based access control (LBAC).
Run the following command against every database in every instance in the DB2 copy:

SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABLES
WHERE SECPOLICYID>0

For the DB2 Performance Optimization Feature:

Check whether there are any materialized query tables.
Run the following command against every database in every instance in the DB2 copy:

    SELECT OWNER, TABNAME
FROM SYSCAT.TABLES WHERE TYPE=’S’

Check whether there are any multidimensional cluster tables.
Run the following command against every database in every instance in the DB2 copy:

SELECT A.TABSCHEMA, A.TABNAME, A.INDNAME, A.INDSCHEMA
FROM SYSCAT.INDEXES A, SYSCAT.TABLES B
WHERE (A.TABNAME=B.TABNAME AND A.TABSCHEMA=B.TABSCHEMA)
AND A.INDEXTYPE=’BLOK’

Check whether any of your instances use query parallelism (also known as interquery parallelism).
Run the following command once in each instance in the DB2 copy:

    SELECT NAME, VALUE
FROM SYSIBMADM.DBMCFG
WHERE NAME IN (‘intra_parallel’)

Check if connection concentrator is enabled. Run the following command against every instance in the DB2 copy:

    db2 get dbm cfg

This command displays the current values of database manager configuration parameters including
MAX_CONNECTIONS and MAX_COORDAGENTS. If the value of the MAX_CONNECTIONS is greater than the
value of the MAX_COORDAGENTS then connection concentrator is enabled. If you are not using
DB2 Enterprise Server Edition, DB2 Advanced Enterprise Server Edition, or DB2 Connect™ Server
products, ensure that connection concentrator is disabled. This is because connection concentrator
is only supported for DB2 Enterprise Server Edition, DB2 Advanced Enterprise Server Edition, or
DB2 Connect Server products.

For the DB2 Storage Optimization Feature:

Check if any tables have row level compression enabled.
Run the following command against every database in every instance in the DB2 copy:

SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABLES
WHERE COMPRESSION IN (‘R’, ‘B’)

Check if any indexes have compression enabled.
Run the following command against every database in every instance in the DB2 copy:

SELECT TABSCHEMA, TABNAME, INDNAME, COMPRESSION
FROM SYSCAT.INDEXES
WHERE COMPRESSION = ‘Y’

Check if any compression dictionary still exists for a table that has row level compression deactivated.
Run the following command against every database in every instance in the DB2 copy:

SELECT TABSCHEMA, TABNAME
FROM SYSIBMADM.ADMINTABINFO
WHERE DICTIONARY_SIZE <> 0 OR XML_DICTIONARY_SIZE <> 0

Note: This query might be resource intensive and might take a long time to run.
Run this query only if Storage Optimization license violations are being reported
even though there are no tables that have row level compression enabled.


Informix 12.10.xC5 is out!

Informix 12.10.xC5 is out

https://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.po.doc/new_features_ce.htm?lang=en-us#newxc5_toc

– AUTO_REPREPARE changes,

– Rolling upgrades for high-availability clusters

– Support for Java 7

– Improved installation logging and debugging

– Easier silent installations

– Tenant databases session_limit_memory,session_limit_logspace,session_limit_txn_time,tenant_limit_space

– Limit access to tenant databases in OAT

– Limit session resources SESSION_LIMIT_MEMORY,SESSION_LIMIT_TEMPSPACE,SESSION_LIMIT_LOGSPACE,

SESSION_LIMIT_TXN_TIME

– Larger maximum tape size for backups TAPEDEV/LTAPEDEV 9 ZB

– Informix JDBC Driver now supports the Estonian and Lithuanian locale, et_ee

– Correlated aggregate expressions

– Control repreparation IFX_AUTO_REPREPARE new values

– Manipulate JSON and BSON data with SQL statements

– High availability for MongoDB and REST clients

– Wire listener configuration enhancements

– Wire listener query support, Join/Array queries on JSON data

– Enhanced account management through the wire listener,JSON lockAccount and unlockAccounts commands

– Load pure JSON documents into time series, TSL_PutJson

– Faster loading of time series data files
TSL_Put function can now contain JSON or BSON documents as values
for columns other than the primary key and time stamp columns
– Improved logging for the time series loader
You can choose to retrieve loader messages from a queue instead of logging the
messages in a message log file. Retrieving messages from a queue results in less
locking contention than logging messages in a file.
TSL_GetFmtMessage/TSL_GetLogMessage/TSL_MessageSet

– Create new time series while loading data,TSL_SetNewTS/TSCreateVirtualTab

– Display time series storage space usage,TSInfo

– View active time series loader sessions,TSL_ActiveHandles

– Analyze time series data for matches to patterns,TSPatternMatch/TSCreatePatternIndex

– Clip selected columns of time series data,ProjectedClip

– Track moving objects
The spatiotemporal search extension depends on the TimeSeries and spatial extensions.
You store the spatiotemporal data in a TimeSeries data type with columns for longitude and latitude.
You index and query the spatiotemporal data with the new spatiotemporal search functions.
You can also query spatiotemporal data with time series and spatial routines.

IBM Informix Spatiotemporal Search for Moving Objects User’s Guide.
https://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sts.doc/sts.htm?lang=en-us
– Enhancements to Informix Warehouse Accelerator
– accelerate queries that include these scalar functions: CURRENT, SQRT, and SYSDATE.
– ondwa listmarts command
– load data marts faster by adding a second DWAVP virtual processor
The DWAVP virtual processor runs Informix Warehouse Accelerator administrative
functions and procedures.


sql server 2012 sp2 cu5 is out

sql server 2012 sp2 cu5 is out – https://support.microsoft.com/en-us/kb/3037255

FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING

https://support.microsoft.com/kb/3034679

FIX: Complex parallel query does not respond in SQL Server 2012

When starvation of worker threads occurs, a specific wait between worker threads is not treated as a potential blocker. Therefore, the deadlock monitor does not detect the condition.

https://support.microsoft.com/en-us/kb/3037624

FIX: Data Alerts tables are filled up and never be cleared old data when you use alerts for SSRS 2012 reports

https://support.microsoft.com/kb/3038332

FIX: Error 4360 when you restore the backup of secondary replica to another server in AlwaysOn Availability Groups

https://support.microsoft.com/kb/3038943

FIX: “Access Denied” error occurs when you run an XML validation task in SSIS 2012

https://support.microsoft.com/kb/3040804

FIX: Performance issue occurs when you use sqlncli11.dll ODBC driver to access SQL Server in an application

https://support.microsoft.com/kb/3041859

FIX: Access violation may occur when you run a query against the sys.dm_exec_procedure_stats DMV joined on plan_handle

https://support.microsoft.com/kb/3042007

FIX: MDX query returns incorrect results when you run it with subselect after an UPDATE CUBE statement in SSAS 2012

https://support.microsoft.com/kb/3042077

FIX: Access violation and “No exceptions should be raised by this code” error occur when you use SQL Server 2012

https://support.microsoft.com/kb/3042135

FIX: SQL Server 2012 shuts down unexpectedly when you run DBCC CHECKDB and the rollback recovery on the snapshot fails

https://support.microsoft.com/kb/3044958


27th January 2015 Cardiff SQL Server Usergroup Questions.

Can you setup replication in SSDT?

How does non-overwrite work?

What editions is replication in?

https://msdn.microsoft.com/en-us/library/cc645993.aspx#Replication

Can you reduce service account DDL privileges for security, also capt?


Oracle varchar2 and “nonpadded comparison semantics”.

The Oracle varchar2 data type has “nonpadded comparison semantics” as per

http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1822

This means values with trailing whitespace can be stored e.g. Under version 12.1.0.2.0:

SQL> create table a ( b varchar2 (30));

Table created.

SQL> insert into a values (‘abc ‘);

1 row created.

SQL> select ‘x’||b||’x’ from a;

‘X’||B||’X’
——————————–
xabc x

but not directly compared against a value with trailing whitespace

SQL> select * from a where b=’123 ‘;

no rows selected

We can compare against a value with trailing whitespace by first casting to the CHAR datatype which has “blank-padded comparison semantics”:

select * from a where cast (b as char(30)) = ‘abc ‘;

B
——————————
abc

Using a trigger to fix the issue

SQL> create user c##dave identified by x;

User created.

SQL> GRANT CONNECT,RESOURCE,DBA TO c##dave;

Grant succeeded.

SQL> GRANT CREATE SESSION TO c##dave;

Grant succeeded.

SQL> alter session set current_schema = c##dave;

Session altered.

SQL> create table a ( b varchar2 (30));

Table created.

SQL> CREATE OR REPLACE TRIGGER a_insert_update
BEFORE INSERT OR UPDATE ON a
FOR EACH ROW
DECLARE
my_varchar2 VARCHAR2(30);
BEGIN
— Force to char to remove trailing whitespace
my_varchar2 := :NEW.b;
:NEW.b := RTRIM(my_varchar2);
END;
/  2    3    4    5    6    7    8    9   10   11

Trigger created.

SQL> insert into a values (‘abc ‘);

1 row created.

SQL> select ‘x’||b||’x’ from a;

‘X’||B||’X’
——————————–
xabcx

SQL> drop table a;

Table dropped.

SQL> alter session set current_schema = sys;

Session altered.

SQL> drop user c##dave;

User dropped.