free dataset and data format for testing and playing

https://www.kaggle.com/

Database load testing and benchmarking - HammerDB

HammerDB:

Introducing SQL Server In-Memory OLTP

Introducing SQL Server In-Memory OLTP - Simple Talk: "omething that constantly ev"



How to Create In-Memory Database Tables in SQL Server 2014

Migrating SQL Server Databases to In-Memory OLTP with AMR

OData Services - CodeProject

OData Services - CodeProject:

OData URL sample

http://hldt-yanivt/YanivTZServices/AlisDataService.svc

http://hldt-yanivt/YanivTZServices/AlisDataService.svc/$metadata

//all data
http://hldt-yanivt/YanivTZServices/AlisDataService.svc/t_countries?$format=json

//all id and name
http://hldt-yanivt/YanivTZServices/AlisDataService.svc/t_countries?$select=country_id,country_name&$format=json

//name and id for pk=1
http://hldt-yanivt/YanivTZServices/AlisDataService.svc/t_countries(1)?$select=country_id,country_name&$format=json

//Filter by name
http://hldt-yanivt/YanivTZServices/AlisDataService.svc/t_countries?$select=country_id,country_name&$format=json&$filter=(country_name eq 'Israel')

//Filter by id
http://hldt-yanivt/YanivTZServices/AlisDataService.svc/t_countries?$select=country_id,country_name&$format=json&$filter=(country_id eq 1)

//count
http://hldt-yanivt/YanivTZServices/AlisDataService.svc/t_countries/$count

//order by
http://hldt-yanivt/YanivTZServices/AlisDataService.svc/t_countries?$select=country_id,country_name&$format=json&$orderby=country_name desc


//Insert recort
http://hldt-yanivt/YanivTZServices/AlisDataService.svc/t_countries?$format=json
POST
{
                "_id": 997,
                "_name": "Unknown997",
                "_abbreviation": null,
                "_cee": 0,
                "ent": 0,
                "iban_length": null
}

Connect to SQL Server from Linux via ODBC

unixODBC (it’s a driver manager) and  should be our choice , on top of it we should plug a driver and we can find the  MS-SQL driver from Microsoft.

More details about this one could be found here http://msdn.microsoft.com/en-us/library/hh335190(v=sql.10)

From FAQ : 

The first version of the driver is a 64 bit

 This release supports Red Hat Enterprise Linux 5 and Red Hat Enterprise Linux 6 connecting to SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

So if we are ok with the above restriction  … we can go forward with this driver.

We can Download the Microsoft ODBC for Linux from http://www.microsoft.com/en-us/download/details.aspx?id=28160

you can use Existing MSDN C++ ODBC Samples for Microsoft Linux ODBC Driver as described here http://blogs.msdn.com/b/sqlblog/archive/2012/01/26/use-existing-msdn-c-odbc-samples-for-microsoft-linux-odbc-driver.aspx

info:
Installing and Using the Microsoft SQL Server ODBC Driver for Linux

enjoy 

Yaniv Tzanany

SQLite - starter kick

SQlite , its very popular Database , easy to use , i used it as InMemory db.

download software from Sqlite home page.

know your limits -> Frequently Asked Questions .

Know your Datatypes In SQLite Version 3.

Command Line Shell For SQLite.

in case you need ODBC for sqlite , you can find it here SQLite ODBC Driver.
nice tool for sqlite its the sqlite browser.
check out Sqlite wrappers you might find something for your needs.

(i used the 3.7.10 version)
command line usage example to open db

sqlite3.exe yaniv.db
sqlite> .mode column
sqlite> .headers  on
sqlite> select * from t1;


Create a new db (open cmd window)
sqlite3.exe myTest.db
sqlite> CREATE TABLE t2(t  TEXT,nu NUMERIC,i  INTEGER,r  REAL,no BLOB ,EFFEC_DATE DATE );
sqlite> INSERT INTO t2 (t,nu,i,r,no,EFFEC_DATE) VALUES('500.0', '500.0', '500.0', '500.0', '500.0','2010-10-23');
sqlite>.quit


.read excute sql file
import value into db

sqlite>.separator ,
sqlite>.import  dd.txt t2

// will show you all master objects
sqlite> SELECT * FROM sqlite_master;


enjoy 
yaniv Tzanany

memcached - distributed memory db

memcached is a high-performance, distributed memory object caching system, generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load.

Free & open source, high-performance, distributed memory object caching system, generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load.

Memcached is an in-memory key-value store for small chunks of arbitrary data (strings, objects) from results of database calls, API calls, or page rendering.

Memcached is simple yet powerful. Its simple design promotes quick deployment, ease of development, and solves many problems facing large data caches. Its APIis available for most popular languages.

Memcached is in used with the largest sites on earth , so its definitely worth a try , i really hope to test it ASAP.

YanivTzanany

ORACLE locks report script

the next script works on oracle 11g , report on all DB locks.

select s.osuser "O/S-User", s.username "Ora-User",s.sid "Session-ID",s.serial# "Serial",

s.process "Process-ID", s.status "Status",l.name "Obj_Locked",l.mode_held "Lock_Mode",

t.used_ublk "Left undo block"

from v$session s,dba_dml_locks l,v$process p,v$transaction t

 where l.session_id = s.sid and p.addr = s.paddr

Yaniv Tzanany

Using embedded DerbyDB

first i created the DB , using the ij tool .
i jar the db directory file into one jar file (dbs.jar).
i add to my class path the derby.jar file and dbs.jar.
i used the next method call
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
DriverManager.getConnection("jdbc:derby:classpath:mydb",m_connectionProperties);

if you want to run a script programmticly ,
The Derby Tools Library(derbytools.jar) contains a class named 'ij' which provides a static method called 'runScript'. This is the key to accomplishing what you want.
NOTE: The 'runScript' method returns the number of errors that occurred during the execution of the script.
http://db.apache.org/derby/javadoc/publishedapi/jdbc4/org/apache/derby/tools/ij.html
  enjoy
Yaniv T

Apache DerbyDB

DB is a Project of the Apache Software Foundation, charged with the creation and maintenance of commercial-quality, open-source, database solutions based on software licensed to the Foundation, for distribution at no charge to the public.

I got the chance to test it and play with it,, as embedded db in my app.
i wrote some notes so i would like to share with you.
to start the db:
cd D:\MyStuff\R_and_D\DerbyDB\db-derby-10.6.2.1-bin\DERBYTUTOR
set DERBY_HOME=D:\MyStuff\R_and_D\DerbyDB\db-derby-10.6.2.1-bin
set PATH=%DERBY_HOME%\bin;%PATH%
setEmbeddedCP.bat
java -jar %DERBY_HOME%\lib\derbyrun.jar ij
CONNECT 'jdbc:derby:mydb;create=true';

running script
run 'cmd.sql';
disconnect
connect 'jdbc:derby:alis;shutdown=true';
Mapping of java.sql.Types to SQL types
http://download.oracle.com/javadb/10.3.3.0/ref/rrefjdbc20377.html
Derby data types
http://db.apache.org/derby/docs/dev/ref/crefsqlj31068.html
online manual
http://db.apache.org/derby/manuals/#docs_10.6
 
Derby supports the following formats for TIMESTAMP:

yyyy-mm-dd hh:mm:ss[.nnnnnn]
yyyy-mm-dd-hh.mm.ss[.nnnnnn]

Derby Date Format:
yyyy-mm-dd
mm/dd/yyyy
dd.mm.yyyy

DATE Test
create table tmstp (c1 DATE);
valid query:
insert into tmstp values ('1990-03-22 10:00:00');
insert into tmstp values ('05/23/2005');
Tuning
from ij command we run the next command
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP','T_X',0);
commit;


CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'T_X', null);
execution plan ->tuning
-- turn on RUNTIMESTATISTICS for connection:
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-- execute complex query here -- step through the result set
-- access run time statistics information:
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);