External Table: VARCHAR in Create but CHAR in Access Parameters Consider this SO Question UTF-8 And External Tables.
A proposed solution contains VARCHAR2 columns in the CREATE section but those same columns get CHAR attributes in the ACCESS PARAMETERS section.

Why the difference?

Datatypes inside the database, and used by SQL*Loader or the ORACLE_LOADER driver are different.

SQL*Loader does not know the VARCHAR2 datatype. It has a VARCHAR type, but that type is nonportable, meaning it is platform dependent.

SQL*Loader Datatypes

  VARCHAR fields can be loaded with correct results only between systems
  where a SHORT data field INT has the same length in bytes. If the byte
  order is different between the systems, or if the VARCHAR field
  contains data in the UTF16 character set, then use the appropriate
  technique to indicate the byte order of the length subfield and of the
  data. The byte order of the data is only an issue for the UTF16
  character set.

The CHAR type of SQL*Loader is portable.

  Portable Datatypes

  The portable datatypes are grouped into value
  datatypes and length-value datatypes. The portable value datatypes are
  as follows:

  CHAR

  Datetime and Interval

  GRAPHIC

  GRAPHIC EXTERNAL

  Numeric EXTERNAL (INTEGER, FLOAT, DECIMAL, ZONED)

  RAW

  The portable length-value datatypes are as follows:

  VARCHARC

  VARRAWC

Using a non-portable type you may or may not be able to load the data on a different platform:

Loading Data Across Different Platforms

  When a data file created on one platform is to be loaded on a
  different platform, the data must be written in a form that the target
  system can read. For example, if the source system has a native,
  floating-point representation that uses 16 bytes, and the target
  system's floating-point numbers are 12 bytes, then the target system
  cannot directly read data generated on the source system.

  The best solution is to load data across an Oracle Net database link,
  taking advantage of the automatic conversion of datatypes. This is the
  recommended approach, whenever feasible, and means that SQL*Loader
  must be run on the source system.

  Problems with interplatform loads typically occur with native
  datatypes. In some situations, it is possible to avoid problems by
  lengthening a field by padding it with zeros, or to read only part of
  the field to shorten it (for example, when an 8-byte integer is to be
  read on a system that uses 4-byte integers, or the reverse). Note,
  however, that incompatible datatype implementation may prevent this.

  If you cannot use an Oracle Net database link and the data file must
  be accessed by SQL*Loader running on the target system, then it is
  advisable to use only the portable SQL*Loader datatypes (for example,
  CHAR, DATE, VARCHARC, and numeric EXTERNAL).


How to enable Oracle Database In-Memory feature in Oracle 11g R2 to reduce report generation time? Problem:
Each report generation takes a few hours in Oracle 11g R2 and a few tens of reports has to be generated every day.

Objective:
To reduce report generation time

Understood:
a) "reports that used to take hours can now be executed in seconds using oracle in-memory database feature".
(Reference: https://blogs.oracle.com/database/oracle-database-18c-%3a-now-available-on-the-oracle-cloud-and-oracle-engineered-systems?elq_mid=104407&sh=26961462026142651578242315&cmid=WWMK170418P00047)
b) The feature is introduced in Oracle 12c and is available in its later versions.

Please advise on:
a) how in-memory/cache table feature of 11g and database in-memory of 12c are related? former one can be used to improve report generation performance?
b) Procedure to enable the in-memory database feature in Oracle 11g R2?
c) Procedure to make standby database of 11g to 12c?

a) + b) The In-Memory Column Store option was introduced in version 12.1.0.2, it is not available in 11g.

Changes in Oracle Database 12c Release 1 (12.1.0.2)

You can cache tables in earlier versions as well, but the new In-Memory option is different. The point of the new In-Memory option is it stores tables in memory in columnar format, instead of the default row level format.

c) Logical standy, Goldengate (requires extra licence) or Streams.


Error "#1118 - row size too large" on the first row of the table only I ran into a problem using a mySQL database. I have some columns as type text but when I try to enter data into the first row I get the error code "#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs" for anything with more than 15 characters. This only occurs for the first row, all the other rows in the column and table work just fine. Help!

Field Type    Collation              Null   Default  Extra
pid   int(11)                        No     None     AUTO_INCREMENT
3     text    utf8_general_ci        Yes    NULL
6     text    utf8_general_ci        Yes    NULL
7     text    utf8_general_ci        Yes    NULL
8     text    utf8_general_ci        Yes    NULL
9     text    utf8_general_ci        Yes    NULL
10    text    utf8_general_ci        Yes    NULL
21    text    utf8_general_ci        Yes    NULL
22    text    utf8_general_ci        Yes    NULL
23    text    utf8_general_ci        Yes    NULL
24    text    utf8_general_ci        Yes    NULL
25    text    utf8_general_ci        Yes    NULL
26    text    utf8_general_ci        Yes    NULL
27    text    utf8_general_ci        Yes    NULL
28    text    utf8_general_ci        Yes    NULL
29    text    utf8_general_ci        Yes    NULL
30    text    utf8_general_ci        Yes    NULL
31    text    utf8_general_ci        Yes    NULL
32    text    utf8_general_ci        Yes    NULL
33    text    utf8_general_ci        Yes    NULL
34    text    utf8_general_ci        Yes    NULL
35    text    utf8_general_ci        Yes    NULL
36    text    utf8_general_ci        Yes    NULL
37    text    utf8_general_ci        Yes    NULL
38    text    utf8_general_ci        Yes    NULL
39    text    utf8_general_ci        Yes    NULL
40    text    utf8_general_ci        Yes    NULL
... 41-59 follow

Indexes:

Keyname Type    Unique  Packed  Field   Cardinality Collation   Null
PRIMARY BTREE   Yes     No      pid     61          A

Server Version: 5.5.9

As suggested from this bug report, you may need to look into the following:

File Format

Run SHOW VARIABLES LIKE 'innodb_file_format%';

innodb_file_format
innodb_file_format_max

You might see values Antelope, Barracuda

Row Format

Run ALTER TABLE tblname ROW_FORMAT=DYNAMIC; to make the row format Dynamic
Run ALTER TABLE tblname ROW_FORMAT=COMPRESSED; to make the row format Compressed

Suggestions

Workaround: use new InnoDB file format Barracuda and row_format=DYNAMIC

This was added to the change log in MySQL 5.5.29

Perhaps an upgrade to the latest MySQL 5.5. is in order

Give it a Try !!!


SQL Server table with 4,000,000 rows is 40GB I have been trying to figure out why our production database has grown so much recently and have found the cause to be an audit table. We are going to archive the table to fix the problem but I just wanted to check if the size of the table looked normal for a large SQL server table like this.
It has 4,000,000 rows with the following structure...

Name                    Type        Nullable
AuditLogId              bigint      no
UserName                nvarchar    no
TimeOfChange            datetime    no
ObjectName              nvarchar    no
ChangeName              nvarchar    no
RecordId                int         yes
OriginalValues          nvarchar    yes
ResultingValues         nvarchar    no

With a primary key on the AuditLogId

ALTER TABLE [dbo].[AuditLog] ADD  CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED
(
    [AuditLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

There is also a foreign key to the record that was updated on RecordId.
The nvarchar fields seem to have up to 12,0000 characters with an average of around 8000 characters.
The table is currently 40GB in size, does that seem right? I can provide more information on the table if that question can’t be answered with what I have posted above.

Thanks

You have 4,000,000 rows and one of the columns averages 8,000 characters (16,000 bytes, I assume).

SELECT CONVERT(bigint,4000000) * /* b */ 16000 / /*kb*/ 1024 / /*mb*/ 1024;
------
61,035

If your stats are accurate, I'd expect this table to be 61 GB. (I wonder if you mean 8,000 bytes, not 8,000 characters, in which case I'd expect > 30 GB.)

Other factors include:

data in other columns I didn't account for in the above calculation
additional indexes
fill factor > 0 and < 100
fragmentation and space still occupied by deleted rows or page splits


Replicate single table from SQL Express to SQL Enterprise 2014 We have a small branch app that stores some data in a SQL Express Database.

We want to have an almost real time copy of this table in our headquarters, were we have SQL Enterprise 2014. I say almost, because it doesn't matter if there is a 5 minute delay or something like that.

The main thing is that it's able to synchronise after a network disconnection between branch and headquarter.

Any solution that does not requiere licensing costs for the branch office?

You can use SQL Server Merge Replication for this.  The SQL Enterprise instance would be the publisher, but Merge Replication can synchronize data both ways.  So changes made at the subscriber would be replicated to the publisher.

If you configure this to be a Push Subscription  the agents will all run at the central instance, and you can schedule them with SQL Agent.


Best design of mongo replica set for DB read/write speed I'm building a Node app that uses MongoDB. Whilst it would probably be fine to have node and mongo sitting on the same VPS, I want to ensure that the MongoDB is backed up and maintain high availability in the case the VPS goes offline.

Looking in to the options available to me, one stood out: creating a replica set.

I have a couple of questions about how best to design the replica set to ensure the maximum speed of my app (as it does a lot of DB read/write):

Surely running mongo on a remote server will slow things down considerably? Instead of performing read/write requests on the same disk, each DB operation has to be sent across datacenters to the remote server. For example, say the ping time between the two servers was 100ms, then making 10 requests would slow down the app by almost 1 second!
With that in mind, would the following setup work?:

Server 1: Node + MongoDB (primary member of replica set)
Server 2: MongoDB secondary member - located in a separate datacenter ideally close to Server 1
Server 3: MongoDB secondary member - located in a separate datacenter ideally close to Server 1

That way, whilst everything is working normally, I benefit from the speed of executing DB commands on the same server as the node app; and when things go wrong, I still have redundancy.

It is not a great practice to mix the app and database on the same server. You're competing for resources. You also mentioned remote execution versus local execution on the same disk. I'd contend that you could suffer from disk contention if it was all on the same disk and the overhead of the remote replication is questionable by comparison. A lot of factors play into that including the type of disk(s) and if you are separating the OS, logs, and data.

Regarding your topology, yes a best practice in any DB technology is to place replica set members in a different datacenter. Pending your disaster recovery requirements, having at least one of those close to the primary node would be beneficial. There are other considerations such as an emergency on one coast of the US for example that makes all 3 unavailable. That is a case by case scenario to consider though. You also should consider your read and write concerns when addressing performance and availability. The default write concern will be 1. If your greater concern is availability; you may want to use a WC of 2 or majority in the example you gave. Testing your workload will only prove this for your use case and the WC can be set per operation so if you have some operations that are less important you could just use a less reliable WC. You should explore the bulk insert method as well. Personally for time-series data I have found the default and maximum batch of 1000 to be very good. I've also had good results increasing it to 5000 and allowing the DB engine break the batches out into 1k batches. Anything after that has demonstrated poorer inserts per second.

It is quite a lengthy topic but these are some ideas.


Query in PostgreSQL with large quantity of squid access requests Hello people, I'm using a log daemon (https://github.com/paranormal/blooper) in Squid Proxy to put access log into PostreSQL and I make a Trigger Function:

DECLARE
  newtime varchar := EXTRACT (MONTH FROM NEW."time")::varchar;
  newyear varchar := EXTRACT (YEAR FROM NEW."time")::varchar;
  user_name varchar := REPLACE (NEW.user_name, '.', '_');
  partname varchar := newtime || '_' ||  newyear;
  tablename varchar := user_name || '.accesses_' || partname;
BEGIN

  IF NEW.user_name IS NOT NULL THEN
    EXECUTE 'CREATE SCHEMA IF NOT EXISTS ' || user_name;

    EXECUTE 'CREATE TABLE IF NOT EXISTS '
    || tablename
    || '('
    || 'CHECK (user_name = ''' || NEW.user_name || ''' AND EXTRACT(MONTH FROM "time") = ' || newtime || ' AND EXTRACT (YEAR FROM "time") = ' || newyear || ')'
    || ') INHERITS (public.accesses)';

    EXECUTE 'CREATE INDEX IF NOT EXISTS access_index_' || partname || '_user_name ON ' || tablename || ' (user_name)';
    EXECUTE 'CREATE INDEX IF NOT EXISTS access_index_' || partname || '_time ON ' || tablename || ' ("time")';

    EXECUTE 'INSERT INTO ' || tablename || ' SELECT $1.*' USING NEW;
  END IF;

  RETURN NULL;
END;

The main function of it is make a table partition by user_name and by month-year of the access, inhering from a master clean table:

CREATE TABLE public.accesses
(
  id integer NOT NULL DEFAULT nextval('accesses_id_seq'::regclass),
  "time" timestamp with time zone NOT NULL,
  time_response integer,
  mac_source macaddr,
  ip_source inet NOT NULL,
  ip_destination inet,
  user_name character varying(40),
  http_status_code numeric(3,0) NOT NULL,
  http_reply_size bigint NOT NULL,
  http_request_method character varying(15) NOT NULL,
  http_request_url character varying(4166) NOT NULL,
  http_content_type character varying(100),
  squid_hier_code character varying(20),
  squid_request_status character varying(50),
  user_id integer,
  CONSTRAINT accesses_http_request_method_fkey FOREIGN KEY (http_request_method)
  REFERENCES public.http_requests (method) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT accesses_http_status_code_fkey FOREIGN KEY (http_status_code)
  REFERENCES public.http_statuses (code) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT accesses_user_id_fkey FOREIGN KEY (user_id)
  REFERENCES public.users (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)

The main problem is get the sum of http_reply_size grouping by user_name and time, my query is:

SELECT
  "time",
  user_name,
  sum(http_reply_size)
FROM
  accesses
WHERE
  extract(epoch from "time") BETWEEN 1516975122 AND 1516996722
GROUP BY
  "time",
  user_name

But this query is very slow in the server (3'237'976 rows currently in 2 days only). So, PostgreSQL has something to optimize a query with that need, or I need to use another SQL or NoSQL system.

You are representing time in 3 different was.  The query is using epoch, while the index is using timestamptz, and the partition constraints are using year and month as two separate fields.  So the query can make use of neither the index nor the partition constraints.

You should probably change them all to use timestamptz.  In the query, instead of converting timestamptz to epoch on the column "time", convert epoch to timestamptz for the BETWEEN constants. (Or just have the client send timestamptz rather than epoch in the first place)

For the check constraint, you could use date_trunc("month",NEW."time") and date_trunc("month",NEW."time" + "1 month") to arrive at the endpoints to put into the check constraint. You would want to spell out the check constraint something like "time" >= low_limit and "time" < high_limit rather than using BETWEEN.


Two columns with and without index in where clause - How index works I am executing a query on SQL Server 2008:

SELECT col1
FROM table1
WHERE col2=val2 AND col3=val3

Here col2 has a non-clustered index, col1 is the PRIMARY KEY, and col3 does not have any index. Query execution plan is similar to this.

I want to know how the query execution works here. From the execution plan I can see an index seek on 'col2' and Key-Lookup on 'col3' (shown parallelly).

Is it going to fetch all the rows that matches the 'col2=val2' condition and check for the other condition?
Why these two -Index seek and Key-Lookup, are shown parallel in the execution plan?
Will it always use the available index on 'col2', considering a large dataset and assuming almost all entries in 'col2' are unique?

I want to know how the query execution works here

The general execution model is a pipeline, where each iterator returns a row at a time. Execution starts at the root iterator (on the far left, labelled SELECT in your example).

After initialization, the root iterator requests a row from its immediate child, and so on down the chain until an iterator that can return a row is found. This passes back up the chain to the root where it is queued for despatch to the client. That is a very simplified overview, for more details see:

Showplan Logical and Physical Operators Reference (Product Documentation)
Iterators, Query Plans, and Why They Run Backwards (me)
A Second in the Life of a Query Operator by Brad Schulz

  Is it going to fetch all the rows that matches the 'col2=val2' condition and check for the other condition?

The nonclustered index seek will locate a row that matches col2=val2. It will be able to return col2 and col1 (see its output list) because col1 is present in the index (since the primary key is clustered in this case).

This row is passed up to the nested loops join, which then passes control to the key lookup. The lookup uses the col1 value to seek into the clustered index b-tree to find the value of col3 in this row. The value is tested against the predicate col3=val3 and only returned if it matches.

If there is a match, the row (c1, c2, c3) is passed up the chain and queued for transmission to the client. As control descends the tree again, any new match for col2 in the nonclustered index will result in a repeat of the nested loops join -> lookup -> return row cycle. As soon as the nonclustered index seek runs out of rows, the process completes when control next returns to the root iterator.

  Why these two -Index seek and Key-Lookup, are shown parallel in the execution plan?

That's just the way the graphical plan is laid out. See the links and discussion above for the correct way to understand the execution process in detail.

  Will it always use the available index on 'col2', considering a large dataset and assuming almost all entries in 'col2' are unique?

Most likely yes. The optimizer makes a cost-based choice between the available strategies. With very few matches expected, the optimizer will usually assess a nonclustered index seek with a lookup as being the cheapest option. An exception occurs when the table is very small. In that case, scanning the clustered index and applying both predicates to each row will likely look cheapest.

As a final note, a covering nonclustered index that would avoid the lookup is:

CREATE [UNIQUE] NONCLUSTERED INDEX [dbo.table1 col2,col3 (col1)]
ON dbo.table1 (col2, col3)
INCLUDE (col1);

It should be specified UNIQUE if that is true for (col2, col3).


Consistent Database Export of Oracle Database I'm quite confused and I need some clarification!

I do full export of database with Export Pump (for replication/duplication).

What I usually do is:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP RESTRICT

Then export, so I can have a consistent export (DMP-file).

Now can I export only with the parameter CONSISTENT=Y and/or FLASHBACK_SCN=SOME_NUMBER?
What is the differences (if any), and when to use them?

I hope some one explain it to me in simple language!

What are the best practices in this regard?

Data Pump command-line arguments are partially backward-compatible with Export and Import utilities, and CONSISTENT maps to FLASHBACK_TIME.

FLASHBACK_SCN is used to specify system change number until all changes from the source database are exported. FLASHBACK_TIME allows you to specify change time. When you specify CONSISTENT, Data Pump Export determines the current time and uses that value for FLASHBACK_TIME implicitly.

For more info read Oracle Database Utilities.


How to manage a growing MySQL slow_log table? I see MySQL supports storing slow_log as a table (internally using mysql/slow_log.CSV file), and this appears convenient, but is it a feature I should use in production? I found online articles on the importance of temporarily disabling slow query logging while rotating the file log, but what's the best practice for managing slow_log.CSV? eg. copy and truncate table or rotate the slow_log.CSV just like db-slow.log?

First of all, I discourage you from using the slow log with TABLE output, in production. The reason is that writes are locking, which limits the concurrency of your workload.

Moreover, CSV stored engine does not support indexes. Any non-trivial query will be very slow, so I don't see any advantages in doing this. Of course you could use MyISAM and add indexes, but then writes will become more expensive.

That said, the correct way to "rotate" the table is copying it and than truncate it. You will probably lose some queries every time: the ones ran after the copy but before the TRUNCATE TABLE statement.


Create Role or Create user PostgreSQL version : 11.2
OS platform : RHEL 7.6

I am new to PostgreSQL. I have a CRM application whose data I want to store in my 11.2 PostgreSQL Database.

The Database name will be : crm_db
The schema name will be   : crm_app

I want all the schema objects in crm_app schema to be owned by a database user called crm_usr.

Can I just create a DB user using CREATE USER command like below or should I create a ROLE called crm_usr ? What is the recommended way to do this in 11.2 (the latest version at the time of posting this ie.23-Feb-2019)

CREATE user crm_usr identified by tiger235;

CREATE DATABASE crm_db OWNER crm_usr;

Note that in PostgreSQL a user and a role are the same thing. The difference is that a user can log in and a role cannot. So, to answer your question you need to decide if you want crm_usr to be able to login or not.

The approach that I try to take is to have two (or more) roles-- one for owning (i.e. xyz_owner) the objects and one or more (xyz_user) for performing the DML (selects, inserts, updates, etc.) on those objects. xyz_user gets granted to the actual users of the database and xyz_owner doesn't get granted to anyone.


Requesting help with setting up design of a few tables and the correct foreign keys needing some help setting up some tables and their foreign key constraints.

3 total tables:

UserTable - contains:

UserID
Other Fields

ShapeTable - contains:

ShapeID
UserID (from UserTable)
Other Fields

RecipientTable - contains:

RecipientID
UserID (from UserTable)

A user is able to draw a shape, or many shapes. A user can also add potentially many recipients. A shape can have 0 or many user's recipients added to it.

If a recipient is deleted by user, it should be removed from all stored shapes. If a shape is deleted by user, it should not delete recipients.

I'm struggling with how to design the "mappings" between the Shapes and the Recipients for each user.

When a user draws a shape, I want to present all the recipients for the user that drew the shape. The user will then be able to select 0, 1, or many of those recipients for that given shape. I'll need to be able to retrieve this later on as well. When a shape is deleted, I want it to delete the "mappings" to any recipients as well, but not necessarily delete the actual recipient record.

What is the best way to arrange the tables, should there be an additional "mappings" type table created, and what foreign key constraints should exist to enforce cascading and referential integrity?

Let me know if there's a better way to present the question if I left something out. I am using MySQL (InnoDB) and PHP (if that matters).

With a many-to-many relationship (each shape can be associated with many recipients, and each recipient can be associated with many shapes).

Here's the create statements for the mapping table (plus my dummy definitions for the three tables you've already got):

CREATE TABLE `user` (UserID int AUTO_INCREMENT NOT NULL,
                     userVal varchar(32),
                     CONSTRAINT pk_user
                     PRIMARY KEY (UserID)
                    );

CREATE TABLE `shape` (ShapeID int AUTO_INCREMENT NOT NULL,
                      UserID int,
                      shapeVal varchar(32),
                      CONSTRAINT pk_shape
                      PRIMARY KEY (ShapeID),
                      CONSTRAINT fk_shape__user
                      FOREIGN KEY (UserID)
                      REFERENCES `user` (UserID)
                     );

CREATE TABLE `recipient` (RecipientID int AUTO_INCREMENT NOT NULL,
                          UserID int,
                          recipientVal varchar(32),
                          CONSTRAINT pk_recipient
                          PRIMARY KEY (RecipientID),
                          CONSTRAINT fk_recipient__user
                          FOREIGN KEY (UserID)
                          REFERENCES `user` (UserID)
                         );

CREATE TABLE map_shape_recipient
       (ShapeID int,
        RecipientID int,
        CONSTRAINT pk_map_shape_recipient
        PRIMARY KEY (ShapeID, RecipientID),
        CONSTRAINT fk_map_shape_recipient__shape
        FOREIGN KEY (ShapeID)
        REFERENCES shape (ShapeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
        CONSTRAINT fk_map_shape_recipient__recipient
        FOREIGN KEY (RecipientID)
        REFERENCES recipient (RecipientID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
       );

You'd probably want the same ON DELETE and ON UPDATE for the shape and recipient foreign keys, but you didn't specify, so I left that out.

Tested via db-fiddle; that has the four CREATE TABLE statements, some sample data, and shows what happens in the mapping table when a shape or a recipient is removed.

NOTE: Presumably, a recipient and a shape should only be associated if they belong to the same user.

You might be able to make the primary keys of shape and recipient the unique ID from the table combined with the UserID. map_shape_recipient would require the UserID column too, and (if there's only one and it's part of the foreign keys to shape and to recipient) they'd have to have the same UserID.

Technically at least, this would be a denormalization (the map table wouldn't need the UserID because ShapeId and RecipientID both uniquely identify one row in their respective tables).

A more solution would be an INSERT trigger on the map table, that prevents a row from being inserted unless the UserID from the shape row and the UserID from the recipient row match.

In your case, your application should prevent associating a shape with a recipient that doesn't have the same UserID. I would still recommend some mechanism to ensure relational integrity, in case someone directly interacts with the database outside of the application.


How to run db2pd -hadr in batch mode I am trying to automate some things on DB2 and would like to use a batch file to do this. I create the following batch file test.bat:

db2cmd - -w "db2 -tvf test.db2"

I then try to do two commands in the test.db2 script, which work together in the CLP interactive mode:

!set db2instance=INST1;
!db2pd -db MYDB -hadr;

I then get the following output:

  Database MYDB not activated on database partition 0.
  Option -hadr requires -db  or -alldbs option and active database.

I'm using DB2 9.7 LUW on Windows 2008 R2.

I found a way to do this using two batch files:

test1.bat
------------------------------------------------------------------------
db2cmd -c -i -w "test2.bat"
PAUSE

test2.bat
------------------------------------------------------------------------
set db2instance=INST1
db2pd -db MYDB -hadr

This is effectively the same as running in interactive mode but if anybody has a way to do this using only one batch file please let me know!


THROW is not returning the correct line number when variables are used An often mentioned benefit of THROW in a CATCH block is that it returns correct line number where the error occurred.
I noticed that it only does this when I use THROW without any variables.
Using the format below, with my own error number and message returns the line number of the THROW itself (just like RAISERROR does).

THROW 50055, 'my message', 1;

Is this to be expected and a known flaw or am I doing something wrong?

I'm using SQL Server 2017 CU20.

Is this to be expected and a known flaw or am I doing something wrong?

Yes, it is the expected behavior. You've generated a new error message, and it originates at the call site of THROW ....  This is why you should favor just THROW; or you need to encapsulate all the relevant error context information in your message.


Error: "mod_fcgid: can't apply process slot for" makes my server unresponsive for a period of time Following error keeps appearing in my PHP error_log

mod_fcgid: can't apply process slot for

So far I pinpointed this issue to following settings in httpd.conf

FcgidMaxProcessesPerClass 8
FcgidMaxProcesses 20

By default these settings were set to 8 and 20, this would result in periodical freezes of my server (2 times per hour for 1 - 2 minutes).

I modified these settings to the following

FcgidMaxProcessesPerClass 1000
FcgidMaxProcesses 1000

With these new settings of 1000 each, server freezes occur only once per day but for as long as 10 - 30 minutes.

--What I mean by server freezes--

Both PHP and MySQL server is unresponsive. 500 error is returned to the client after a minute or longer.

This freezing behavior stop completely on its own, everything goes back to normal after stated time above.

High server load is not likely to be the issue, since these freezes occur randomly each day. Both CPU and RAM is at 3% - 6% when freezes occur.

There is surprisingly little information about this error out there.

My server provider suggested that FastCGI might be a poor PHP interpreter for potentially high traffic websites. I switched my interpreter to NGINX with support of PHP-FPM.

I dont have enough data yet to confirm that freezing was solved completely (but now it wont be related to FastCGI), NGINX is the preferred option for maintaining high performance server as suggested by various sources.


RETURN NEXT in Postgres Function I'm currently writing a plpgsql function that will select from a table and produce a substantial amount of data.

I was originally going to return an array, but I found a resource that explains the "RETURN NEXT operation - Very handy for callers.

However, I read this blurb which is concerning:

  Note: The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, performance might be poor:

Is there a Postgres feature that would allow my function to stream the results back to the caller as they're produced? This might be a limitation of Postgres' architecture in general, but I want to make sure I'm not overlooking something!

If the results are not meant to be used in a subquery but by code, you may use a REFCURSOR in a transaction.

Example:

CREATE FUNCTION example_cursor() RETURNS refcursor AS $$
DECLARE
  c refcursor;
BEGIN
  c:='mycursorname';
  OPEN c FOR select * from generate_series(1,100000);
  return c;
end;
$$ language plpgsql;

Usage for the caller:

BEGIN;
SELECT example_cursor();
 [output: mycursor]
FETCH 10 FROM mycursor;

 Output:

 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10

CLOSE mycursor;
END;

When not interested in piecemeal retrieval, FETCH ALL FROM cursorname may also be used to stream all results to the caller in one step.


Is the official term "template database" or "maintenance database"? pgAdmin has a field for the "maintenance database". The PostgreSQL manual talks about template databases.
What is the correct term? Why are they not consistent? This is even more confusing since PostgreSQL has "recently" changed the name from "template1" or "template0" into "postgres", suggesting that they didn't like the term "template database", yet continue to use it in the manual... while pgAdmin uses "maintenance database".
And I only vaguely understand the purpose for this to exist. I just know that whenever I have attempted to code a script which does the initial installation of a database, there is nothing for it to "connect to", so that's when I need to use the "maintenance database" when connecting, only to immediately CREATE DATABASE...

You must be an old-timer. The postgres database has been around since version 8.1.
There are three notions:

A template database is a database whose purpose is to be used as a template in CREATE DATABASE: the template is essentially copied.
While in principle any database can be used as template, template databases typically have datistemplate set to TRUE.
Initially PostgreSQL comes with two templates: template1 (which you can modify if you want) and template0 (which remains unmodified).

A default database called postgres. The purpose of this database is for administrative connections, typically to execute the CREATE DATABASE SQL statement.
Before 8.1, when this database was introduced, template1 did double duty for that purpose. The 8.1 release notes describe that well:

In prior releases, template1 was used both as a default connection for utilities like createuser, and as a template for new databases. This caused CREATE DATABASE to sometimes fail, because a new database cannot be created if anyone else is in the template database. With this change, the default connection database is now postgres, meaning it is much less likely someone will be using template1 during CREATE DATABASE.

The maintenance database is a term unknown in PostgreSQL, it exists exclusively in pgAdmin and is a frequent source of confusion.
In that text field you enter the name of the database to which you want to connect. Just forget the “maintenance” part.
The designers of pgAdmin probably intended people to enter postgres there, assuming that the tool is used by a DBA who has access to that database. Then you can click on other databases of the same cluster, and pgAdmin will open an additional database connection to those databases.


Was 'ActiveX Scripting' step type secretly removed from SQL Server 2016? Migrating our database from SQL Server 2012 to 2016, we can't recreate an SQL Agent job that involves an ActiveX Scripting step written in VBScript.

The error message suggests that 'ACTIVESCRIPTING' is not an allowed value for the @subsystem parameter of sp_add_jobstep.

I know ActiveX Scripting is marked as "will be removed in a future version of SQL Server". However I cannot see anything about it actually being removed in this version of SQL Server.

SQL Server documentation seems to suggest it is still available in SQL Server 2016:

Create an ActiveX Script Job Step (SQL Server 2016)
Documentation on sp_add_jobstep, updated in March 2017, still lists @subsystem ='ACTIVESCRIPTING' as available.

Was @subsystem ='ACTIVESCRIPTING' in fact secretly removed in an undocumented way, or did we miss an installable component that enables it?

Discontinued Database Engine Functionality in SQL Server 2016 indicates

ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.


Options for Index Defragmentations For Critical Tables I wonder if this is a suitable solution for index defragmentations.
scenario is like this;

There is a table named 'Events' which is very critical and has 4 indexes which are not done maintenance before and fragmentation rates are around 95. Also table has 450 million rows. There are very much transactions in a minutes and Board can not tolerate to cuts for long time like 30 minute or higher.

I want to create a new table named 'Events_2' and create same indexes and statistics after all process done, I want to drop 'Events' table, and rename 'Events_2' to 'Events'.

Is it a solution for this situation or Index rebuild or Reorganize is the only solution?

Thanks For Reply.

I would not recommend the procedure that you mention in your question. If you did that, the  newly built table would end up suffering the same amount of fragmentation as the original and you would be back to square one.

I would recommend using Ola Hallengren's Index and Maintenance scripts to reduce the fragmentation of your indexes:-
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola's script are tried and tested, I've used them for a while now and have had absolutely no problems with them.

So, taking example H from Ola's page.

Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks:-

EXECUTE dbo.IndexOptimize
@Databases = 'AdventureWorks',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'AdventureWorks.Production.Product

This will REORGANIZE all indexes on the Production.Product table with fragmentation > 5% and < 30%. It will then REBUILD_ONLINE all indexes on the table with fragmentation > 30%.

The values are based on Microsoft's recommendation here:-
http://msdn.microsoft.com/en-us/library/ms189858.aspx

REORGANIZE is an online operation, leaving the table available for read-write operations.

REBUILD ONLINE can be run as you have Enterprise Edition. Quoting from BOL:-

  "The ONLINE option allows concurrent user access to the underlying
  table or clustered index data and any associated nonclustered indexes
  during these index operations. For example, while a clustered index is
  being rebuilt by one user, that user and others can continue to update
  and query the underlying data"

But be aware, it should be called REBUILD NEARLY ONLINE as a S-lock or Sch-M lock is held at the end of the operation which can block certain operations. Also online rebuilds cannot be performed on indexes containing the following data types:- image, ntext, and text.

Have a read through of Microsoft's guidelines for online operations:-
http://msdn.microsoft.com/en-us/library/ms190981.aspx

Once you get familiar with the scripts on a DEVELOPMENT system you can look at implementing on your PRODUCTION server. A proper schedule should be setup in order to keep the amount of REORGANIZE/REBUILD that have to be run to a minimum.


Issue With SSRS Emailing Reports As PDFs I have a strange issue that I'm unable to find the answer to online or by looking at it myself.

Basically when I set up an email subscription for a report I've created in SSRS Report Builder (2008); I can render my report into any format other than PDF and it will be sent to me successfully. The report is a table that returns some client information from our recruiting database; nothing complicated and it does work correctly if I choose another format.

If I try and send as a PDF I receive the error:
'Failure sending mail: The transport lost its connection to the server.'

I can export the report into PDF format if I run it from the report designer and the file size for the fully rendered PDF is ~35kb so I can't imagine a timeout.

It is baffling me and I'd like to know the root cause.

Thanks for your time.

Another part of this puzzle was that the files that weren't able to send were created in a temporary location where they would then be deleted.

This issue is now completely resolved - in brief (after several false starts such as checking file/folder permissions and discovering reports would be sent via subscription as pdf if they contained a graph and not a matrix); we ended up using SMTP (set up on SQL Server) to send the email from the SQL Server to Exchange, which would then handle the rest of the journey.

Thank you all for your suggestions.


MySQL Adding New Slave to Master Master Replication Scheme I've got two mysql servers, A and B which are currently set up in a circular, MASTER MASTER replication scheme. I need to add third server to the mix, C as a SLAVE to B as pictured below:

A <---> B
        |
        v
        C

I just made an attempt at doing this, but I forgot to take into account the fact that writes that go from A to B will not make it to C without turning on log-slave-updates on B being turned on.

My question: if I turn log-slave-updates on for B, isn't A going to pick up on those log entries again execute them a second time, thereby spawning some sort of black hole that could threaten to swallow the planet?

As long as the values of server-id are different for each server, no planets will be swallowed.  Not even Pluto.

I suggest you turn on binlog and log_slave_updates on all servers.  Then rebuild C from B (as, I assume, you did once).  Then proceed.  I would not want to try to do it from the current C, which is missing stuff.


mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade? I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway around this?

I have addressed something like this already

Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
Jan 31, 2014 : Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?
Apr 11, 2013 : MySQL upgrade 5.0.88 to latest
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

Leaping two versions is risky if you do not want to run mysql_upgrade twice. My Oct 17, 2014 post mentions running mysql_upgrade twice, the right way.

In your case, you should do this:

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="set group_concat_max_len = 1048576;"
SQL="${SQL} select group_concat(schema_name) from information_schema.schemata"
SQL="${SQL} where schema_name not in"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
DBLIST=`mysql -ANe"${SQL}" | sed 's/,/ /g'`
mysqldump --databases ${DBLIST} > MySQLData.sql
SQL="SELECT CONCAT('SHOW GRANTS FOR ',"
SQL="${SQL} QUOTE(user),'@',QUOTE(host),';') "
SQL="${SQL} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLGrants.sql
rm -f GetGrants.sql

Now just load MySQLGrants.sql and MySQLData.sql into the MySQL 5.6 instance.

GIVE IT A TRY !!!


Strange behavior of TOP 1 clause sql server I have a SQL Server database and restored it on another server on a different machine. Now I run following same query on both DBs which produces same below result set.
select person_id, visit_id, verification_code, mobileNo, created_date
from person
order by verification_code, created_date DESC

But when I add TOP 1 clause to the above query, original DB shows the 2nd row (ignores first row)
select TOP 1 person_id, visit_id, verification_code, mobileNo, created_date
from person
order by verification_code, created_date DESC

while restored DB shows first row

I thought it might be due to different collation, but I checked both servers have same collation.

But the data in your order by clause doesn't provide any ordering
verification_code is null
and all values for
created_date are the same
you have to give the engine a little bit to work with


Is it possible for me to be notified if an 'after insert or update trigger' stops working? Last week, I found my after insert or update trigger wasn't working. After I disabled and enabled it, it started working again.

I do not yet know why it stopped working. Is there any way to deal with this?  Because this trigger is recording the value of daily jobs, and is used for report purposes. If this trigger goes dead in few days without my notice or error, I will be in hot water.

I am using Oracle 10g, access the db by using sqldeveloper

My Trigger

create or replace
TRIGGER MASTER.INSTANCE_STEP_TRG
AFTER INSERT OR UPDATE OF SYSID,STEP_ID,INSTANCE_ID,PARENT_STEP_ID ON MASTER.WF_INSTANCE_STEP
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
WHEN (new.sysid > 0)
declare
stepSysid number;
crCode varchar(50);
crDate date;
step_id number;
BEGIN
  step_id := :new.step_id;

  select ss.sysid into stepSysid from TEMPLATE.wf_step ws
  inner join TEMPLATE.step_stage ss on ss.sysid=ws.stage_id
  where ws.sysid= step_id;

  if ( stepSysid>0) then
    insert into MASTER.fact_cr_progress values(0,:new.instance_id,stepSysid,:new.create_dt);
  end if;

  dbms_output.enable(10000);
  dbms_output.put_line('start print');

END;

A trigger cannot simply stop working.

A trigger can be disabled.  A trigger can be dropped.  A trigger will be made invalid if DDL is done to one of the objects it references but it will still be executed if the triggering statement is executed.  If the trigger fails to recompile successfully, the triggering statement will get an error

ORA-04098: trigger ' MASTER.INSTANCE_STEP_TRG' is invalid and failed re-validation

It also seems possible that the trigger was working correctly as declared but not as you intended.  For example, it is possible that the new.sysid was not greater than 0 (remember that NULL is not greater than 0) causing the WHEN clause to not be met.  It is possibly that your SELECT statement returned a value less than or equal to 0, thus causing your INSERT not to be executed.  If either TEMPLATE.wf_step or TEMPLATE.step_stage has a column STEP_ID, the WHERE clause

where ws.sysid= step_id;

will interpret STEP_ID as the column in the table, not your local variable STEP_ID.  One of the reasons that PL/SQL developers generally add prefixes to local variables (i.e. L_STEP_ID rather than STEP_ID) is to avoid inadvertently using a name that is also used by a column in a table because these sorts of scope resolution problems are notoriously difficult to debug.


PostgreSQL: permission denied for relation I'm a bit confused about setting permissions in PostgreSQL.

I have these roles:

                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication | {}
 meltemi   | Create role, Create DB                         | {rails}
 rails     | Create DB, Cannot login                        | {}
 myapp     |                                                | {rails}

and databases:

                                    List of databases
        Name         | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
---------------------+--------+----------+-------------+-------------+-------------------
 myapp_production    | rails  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 ...

user myapp has no problem querying the myapp_production database adding & deleting records. I'd like for meltemi to also be able to query the same database. So, I created a role rails which owns the database and made both meltemi and myapp members of rails. But I still get permission denied for relation errors. Meltemi can view the schema but can't query the DB.

I just noticed (with \dt command) that myapp is the owner of the tables:

             List of relations
 Schema |       Name        | Type  | Owner
--------+-------------------+-------+-------
 public | events            | table | myapp
 public | schema_migrations | table | myapp
 ...
 public | users             | table | myapp
 ...

The tables were created via an ORM (Rails' ActiveRecord migrations).

I know authorization is very different in PostgreSQL (as opposed to MySQL & others I've used). How should I be setting up my database so that different users can access it. Some should be able to CRUD but others may only be able to Read, etc...

Thanks for any help. Sorry, I know this is a very basic question but I haven't been able to find the answer myself.

I just wrote about this in my answer to Granting rights on postgresql database to another user on ServerFault.

Basically, the best solution when you have a single user and you want to grant other users the same rights is to turn that user into a group, create a new user with the same name as the original one that's a member of the group, and grant that group to other users too.

So in your case, rails gets renamed to say myapp_users, then you create a new login role (user) named rails and GRANT myapp_users TO rails. Now you an GRANT myapp_users TO meltemi. Both the new rails account and the meltemi user now have the rights of the old railsaccount.

For more fine-grained control I ususally advise that you avoid giving the day-to-day login users or their groups ownership of the tables. Give them access via a NOINHERIT group they must explicitly SET GROUP to, or better, use a completely different user for privileged operations like DDL and GRANTs. Unfortunately this doesn't work with Rails, because Rails likes to apply migrations whenever it feels like it and AFAIK doesn't give you the ability to specify a different, more-privileged user to run the migrations as.


How Can I restore a database to a point of time without Old backup? I have a database in full recovery mode with five tables. I accidentally deleted one table. I don't have any old backups. Can I restore the database with five tables?

If you do not have any backups that contains the deleted table, you can't restore the database to it's original form.


How can I get rows after a specific row, from a resultset of a specific sort order? I have a table that has an ID column of type varchar(255) as the primary key. The rest of the columns comprise of text, datetime and varchar(255) types. Let's consider the given query:
SELECT ID from `table`
where (certain condition)
order by
    columnX,
    columnY,
    ID
limit 10
;

The ORDER BY clause has the specified columns sorted in an ascending order for this example, but it could contain mixed directions (both ASC & DESC). Anyway, executing the above query gives me a resultset like this:
27087675-38b5-41ea-afa5-9cf24c388545
770a933d-064f-41f5-8d2a-55acaedf38bf
a2e509e3-3a61-44e6-a36e-955d7b1f7b5e
19c47c26-075f-4761-a462-02025f4b29ce
ab3418ee-0b26-40ec-87b8-2b3636bb4e59
24caf7cb-fca5-45f0-81a0-f97175d8e071
285295fb-4ba7-4a70-9aaa-0a93cac058e8
f976e378-394f-42ed-af07-012336660546
5c0201c4-4aec-4e48-95c8-91e73b7843ee
de8f6adf-f880-4758-aeda-9e66a308bbc0

The resultset consists of 10 elements (for the sake of brevity). Now, what I'm trying to achieve, is to get the elements after a certain ID, in the same order. For example, if I want to get the elements after ab3418ee-0b26-40ec-87b8-2b3636bb4e59, the output should be
24caf7cb-fca5-45f0-81a0-f97175d8e071
285295fb-4ba7-4a70-9aaa-0a93cac058e8
f976e378-394f-42ed-af07-012336660546
5c0201c4-4aec-4e48-95c8-91e73b7843ee
de8f6adf-f880-4758-aeda-9e66a308bbc0

What I tried:

I tried the condition AND ID > 'ab3418ee-0b26-40ec-87b8-2b3636bb4e59' in the WHERE clause, without any luck.
It kinda works when I use the first column in the ORDER BY clause with the comparison operator, for example AND columnX > 'someValue', but if the ORDER BY clause contains mixture of ASC & DESC, it doesn't work.

Is there any way to solve this? I basically want to split the resultset, which will always have some kind of ORDER. I just want to load the elements after a certain ID, regardless of the sort order. Could really use some help with this.

Plan A. You need the entire set of columns that you are ordering by.  Let's say you left off at a=4 AND b=11 AND c=2
If you are going ascending, then
WHERE (a, b, c) > (4, 11, 2)

For all DESC, change to <
Caveat:  "row constructors" like that were not optimized until 5.7.
For a mixture, please provide a 2-column example without UUIDs.  Provide CREATE TABLE and sample data.  Preferrably ready to run in a 'fiddle'.
Plan B  This is like the row_number(), which is not available before 8.0.
Create a table with a new AUTO_INCREMENT.  If all the work is in a single connection, use a TEMPORARY TABLE.  Then lean on that new id for "remembering where you left off".


format() is a nondeterministic built-in string function... right? Before I post a connect item regarding the lack of documentation about this, will someone confirm that I am not simply missing something here?

On the docs page where format is listed as a string function:

  "All built-in string functions are deterministic." - String Functions (Transact-SQL)

There is also no mention of format being nondeterministic on related pages:

Deterministic and Nondeterministic Functions
FORMAT (Transact-SQL)

However, when attempting to create a persisted computed column:

create table t (date_col date);
insert into t values (getdate());
alter table t add date_formatted_01 as format(date_col,'YYYY') persisted;

Returns the following error:

  Computed column 'date_formatted_01' in table 't' cannot be persisted because the column is non-deterministic.

The documentation states that

  If the culture argument is not provided, the language of the current
  session is used.

but adding a culture argument doesn't change things

This also fails

alter table t add date_formatted_02 as format(date_col, 'd', 'en-US' ) persisted

rextester demo: http://rextester.com/ZMS22966

dbfiddle.uk demo: http://dbfiddle.uk/?rdbms=sqlserver_next&fiddle=7fc57d1916e901cb561b551af144aed6

The FORMAT documentation has now been updated (in response to your Connect item) to say:

  The FORMAT function is nondeterministic.

Likewise, String Functions (Transact-SQL) now includes:

  All built-in string functions except FORMAT are deterministic.


MySQL Cluster - How the Sharding works? Right now I have a MySQL database with a couple of tables with around 20MM rows each; This DB is located on a single server ( 4gb RAM, 60GB storage, 2 CPUs) each table size is 4gb +-, but my server storage is about 75%, and I'll need to upgrade the server.

Instead of growing vertically I want to grow horizontally, after reading about sharding, partitions and clusters i try the cluster solution, using MySQL cluster server.

I quote the MySQL Cluster docs

  MySQL Cluster automatically shards (partitions) tables across nodes,
  enabling databases to scale horizontally on low cost, commodity
  hardware to serve read and write-intensive workloads, accessed both
  from SQL and directly via NoSQL APIs.

My current setup is:

server-db0 - management
server-db1 - SQL node
server-db2 - SQL node
server-db3 - Data node
server-db4 - Data node

Each server 2gb ram 2CPUs 40GB storage

My cluster config file is:

[ndbd default]
NoOfReplicas=2
DataMemory=750M
IndexMemory=200M

[tcp default]
portnumber=2202

[ndb_mgmd]
hostname=10.132.36.121
datadir=/var/lib/mysql-cluster

[ndbd]
hostname=10.132.36.251
datadir=/usr/local/mysql/data

[ndbd]
hostname=10.132.36.104
datadir=/usr/local/mysql/data

[mysqld]
hostname=10.132.36.112

[mysqld]
hostname=10.132.36.117

All is working as expected

-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2    @10.132.36.251  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=3    @10.132.36.104  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.132.36.121  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   2 node(s)
id=4    @10.132.36.112  (mysql-5.6.28 ndb-7.4.10)
id=5    @10.132.36.117  (mysql-5.6.28 ndb-7.4.10)

Memory Usage

ndb_mgm> all report memory
Node 2: Data usage is 0%(68 32K pages of total 24000)
Node 2: Index usage is 0%(81 8K pages of total 25632)
Node 3: Data usage is 0%(68 32K pages of total 24000)
Node 3: Index usage is 0%(81 8K pages of total 25632)

In my tests, I saw that all the data is replicated on each SQL node I inserted 750K rows and the Data usage, and Index usage are the same on both servers.

My main question is

If my big table size is 4GB, I will need to upgrade my node servers to a bigger server with more RAM? For what I see this is not a horizontal growing, I'll still need bigger servers instead of more small servers.

I don't see the how this shards works, I missing something?

Would be great if you point me to the right path.

Thanks in advance.

NDB may be the right solution for you.  Or...

Sharding is splitting up the data -- some rows go here; some go there.  NDB does sharding, and it also does redundancy.  That is, the rows that are going 'here' can be put on 2 servers for redundancy.  So think through whether you want both, and how many servers you want to put into the system.

You have not mentioned anything about running out of CPU or I/O horsepower on your small machine.  16GB/1TB/8-core machines are about the minimum you can buy today; that give you a lot of growth over what you have.

Also, keep in mind that there is a lot of complexity in building and maintaining NDB.  Plus code changes may be necessary.

"2gb ram 2CPUs 40GB" -- Do you run a junk yard?


Installation of Oracle Instant Client on an Ubuntu 64 machine I would like to install Oracle Instant Client on a Ubuntu 64 bit machine.

Following this link, I have installed the rpm packages basic, development and sqlplus using alien.

Then:

sqlplus  username/password@//dbhost:1521/SID
sqlplus: command not found

export ORACLE_HOME=/usr/lib/oracle/12.1/client64
ls $ORACLE_HOME
bin  lib

sqlplus
sqlplus: command not found

I have tried the python script in this link, but this did not work. It conflicts with the installed 12.1 packages as it tries to install 11.2.

Easily solved:

export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

Stick those lines in your ~/.bash_profile if you want them to persist for a given user, or in /etc/bashrc to persist for all users.


existing foreign key that references fields with different datatype (char(8) and varchar(8)) This is more a quest for information that a request for help...

I'm upgrading some old databases (running on sql 2005 and compatibility level 70, I suspect they are online since sql 6.5) to get rid of a couple of old servers and deploy SQL Server 2012 or 2014.

I had to deal with some sql collation now unsupported so I dropped some indexes and foreign keys. While trying to restore them I've been stopped by this error:

  Msg 1778, Level 16, State 0, Line 2 Column 'dbo.Table1.IDColumn' is
  not the same data type as referencing column
  'Table2.ColumnFK' in foreign key
  'Foreign_key'.

The first field is declared as varchar(8) and the second is a char(8) so the message is correct.

I'm doing all the tests on a copy so I double checked on the production database and the foreign key is there, linking a char field with a varchar field.

When scripting the constraint this is what I got (that's the script that is giving error):

ALTER TABLE [dbo].[Table2]
WITH NOCHECK ADD  CONSTRAINT [Foreign_key]
FOREIGN KEY([ColumnFK]) REFERENCES [dbo].[Table1] ([IDColumn])
GO

ALTER TABLE [dbo].[Table2]
NOCHECK CONSTRAINT [Foreign_key]
GO

I found an answer on SO stating that it is not possible to have a foreign key using 2 columns that have different datatype but a workaround is supplied so the issue is not critical.

The question is: was it ever allowed to create a relationship on fields with different data types without tricks? Was there some sort of 'compatibility' between char and varchar?

The answer should be a obvious 'yes, it is possible' but I can't find any evidence stating that this behavior is (was?) allowed and/or expected: I have the foreign key in place but it should not be possible to create it.

Digging into long forgotten boxes I found both nt4 and SQL-Server 6.5 installation media and I can confirm that a foreign key referencing fields with different data types was allowed.

Here is the statement I successfully run on SQL-Server 6.5:

create table tbl1 (code char(8) primary key (code))
create table tbl2 (code varchar(8) primary key (code))
alter table tbl1 add constraint FK foreign key (code) references tbl2 (code)

no trailing semicolon or you'll get a syntax error.

I can't get a grasp on sql 7 media so I can't tell if it was working on that version too.


Update an array element with inc mongo update HI All I have this Data in mongo,

{"articleId" : [
            {
                    "articleId" : "9514666",
                    "articleCount" : 1
            }
    ],

    "count" : NumberLong(1),
    "timeStamp" : NumberLong("1416634200000"),
    "interval" : 1,
    "tags" : "famous"
 }

I want to update it using this new data

 {"articleId" : [
            {
                    "articleId" : "9514666",
                    "articleCount" : 3
            }
            {
                    "articleId" : "9514667",
                    "articleCount" : 3
            }
    ],

    "count" : NumberLong(6),
    "timeStamp" : NumberLong("1416634200000"),
    "interval" : 1,
    "tags" : "famous"
 }

What i need in the output is

{"articleId" : [
            {
                    "articleId" : "9514666",
                    "articleCount" : 4
            }
            {
                    "articleId" : "9514667",
                    "articleCount" : 3
            }
    ],

    "count" : NumberLong(7),
    "timeStamp" : NumberLong("1416634200000"),
    "interval" : 1,
    "tags" : "famous"
 }

Could you please suggest me how can i achieve this this using update operation
My update query will have tags field as query parameter.

I accomplished it with your criteria which was to use the tags field in your query. I of course had to filter on the document further to only update that specific document.
I presumed other documents may have a tag of "famous". I created two documents with tags:famous to prove this worked when updating 1 document. Here's my solution with comments.

//basics
db = test
collection = test

//data looked like this with two documents for testing
/* 0 */
{
    "_id" : ObjectId("5498778567bbbc2fff723515"),
    "articleId" : [
        {
            "articleId" : "9514666",
            "articleCount" : 1
        }
    ],
    "count" : 1,
    "timeStamp" : "1416634200000",
    "interval" : 1,
    "tags" : "famous"
}

/* 1 */
{
"_id" : ObjectId("5498781567bbbc2fff723516"),
"articleId" : [
    {
        "articleId" : "951466622",
        "articleCount" : 22
    }
],
"count" : 1,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
}

//confirmed I can query the one document like this
db.test.find({'tags': 'famous','articleId.articleId':'9514666'})

//was able to execute an update as described
db.test.update({'tags': 'famous','articleId.articleId':'9514666'}, {"articleId" : [
        {
                "articleId" : "9514666",
                "articleCount" : 4
        },
        {
                "articleId" : "9514667",
                "articleCount" : 3
        }
],

"count" : 7,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
})

//Resulting update to document is
/* 0 */
{
 "_id" : ObjectId("5498778567bbbc2fff723515"),
 "articleId" : [
    {
        "articleId" : "9514666",
        "articleCount" : 4
    },
    {
        "articleId" : "9514667",
        "articleCount" : 3
    }
],
"count" : 7,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
}

//showing both documents, only first one updated obviously
/* 0 */
{
"_id" : ObjectId("5498778567bbbc2fff723515"),
"articleId" : [
    {
        "articleId" : "9514666",
        "articleCount" : 4
    },
    {
        "articleId" : "9514667",
        "articleCount" : 3
    }
],
"count" : 7,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
}

/* 1 */
{
"_id" : ObjectId("5498781567bbbc2fff723516"),
"articleId" : [
    {
        "articleId" : "951466622",
        "articleCount" : 22
    }
],
"count" : 1,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
}


Downgrading PostgreSQL 10 to 9.4 Are there any particular precautions I should take when downgrading PG 10 to 9.4?

I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to be able to go back to 9.4.

Things I already tested:

create a backup of the already upgraded PG 10.6 DB (Heroku creates binary compressed dumps)
create a new PG 9.4 DB
upload the backup from step 1. to DB from step 2

The backup seems to be restored properly. What should focus on in particular to be sure this procedure is all right? Are there any other ways to downgrad PostgreSQL?

Downgrading PostgreSQL is not supported.

You may get lucky and it may work if you have a simple database.
Your way is the best way; test the result as well as you can.


How to export large table data into different files in oracle? I have a table which contains 1 million lines and need to export into different files which containing 100k lines each.
sample_01.sql ,sample_02.sql and so on
How can we take export like this in oracle?

You should probably use utl_file and a for loop for this assignment. Then you can open and write one line per file. You should also let people know what a Lakh line is.


SQL datediff for row items I found difficulty with writing SQL statement to calculate the time of each items of row.

My table structure is simple:

TblProduct(Product nvarchar(50), Serial nvarchar(50), Time_Checked datetime)

Below is  my sample data:-

PRODUCT1 Serial1 2014-04-02 07:01:21.000
PRODUCT1 Serial2 2014-04-02 07:02:21.000
PRODUCT1 Serial3 2014-04-02 07:03:21.000
PRODUCT2 Serial1 2014-04-02 08:01:21.000
PRODUCT2 Serial2 2014-04-02 08:02:21.000
PRODUCT2 Serial3 2014-04-02 08:03:21.000
PRODUCT3 Serial1 2014-04-02 09:01:21.000
PRODUCT3 Serial2 2014-04-02 09:02:21.000
PRODUCT3 Serial3 2014-04-02 09:03:21.000
PRODUCT1 Serial4 2014-04-02 10:01:21.000
PRODUCT1 Serial5 2014-04-02 10:02:21.000
PRODUCT2 Serial6 2014-04-02 10:03:21.000

I want to calculate the time for changing product by datediff(minute,start time, end time)
where start time as the last item of each series of product kinds

Start time: PRODUCT1 2014-04-02 07:03:21.000

End time:  PRODUCT2 2014-04-02 08:01:21.000

==>datediff(minute,start time, end time)=58 minutes

Finally, I want to return a result like a table below:-

PRODUCT1 Serial3 PRODUCT2 Serial1 58(minutes)
PRODUCT2 Serial3 PRODUCT3 Serial1 58(minutes)
PRODUCT3 Serial3 PRODUCT4 Serial4 58(minutes)

Use self join with row numbering

SELECT  Productset1.Product,
        Productset1.serial,
        Productset2.Product,
        Productset2.serial,
        CAST(DATEDIFF(minute, Productset1.time_checked, Productset2.time_checked) AS VARCHAR(20)) + ' (minutes)'
FROM    ( SELECT    row_number() OVER ( ORDER BY time_checked ) pid,
                    Product,
                    serial,
                    time_checked,
                    CONVERT(VARCHAR(20), time_checked, 103) datet
          FROM      TblProduct
        ) Productset1
        INNER JOIN ( SELECT row_number() OVER ( ORDER BY time_checked ) pid,
                            Product,
                            serial,
                            time_checked,
                            CONVERT(VARCHAR(20), time_checked, 103) datet
                     FROM   TblProduct
                   ) Productset2 ON Productset1.pid = Productset2.pid - 1
                          AND Productset1.Product <> Productset2.product


Revoking execute access on builtin functions on SQL Server I am trying to lock down a SQL Server database server and I want to revoke execute access from all users on specific builtin functions such as LEFT, RIGHT SUBSTRING, etc.

When I try to execute:

REVOKE EXECUTE ON LEFT from appuser;

I get an error:

  Cannot find the object 'LEFT', because it does not exist or you do not have permission.

In addition, are there any other functions that can perform the same operation on SQL server, i.e. be used to extract portions of a static string ?

Thanks

You cannot restrict permissions to LEFT built-in function.
You can control permissions that are listed by the output of this command:

SELECT *
FROM fn_builtin_permissions(default)


Make queries and BCP faster on a very large table This question is somewhat of a continuation to this:
Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that question here for legibility.

I've written an application with a SQL Server backend that collects and stores an extremely large amount of records. I've calculated that, at the peak, the average amount of records is somewhere in the avenue of 3-4 billion per day (20 hours of operation).

My application receives data, chunks it into batches of ~100k records and creates BCP-ready staging files, moves them to the server and SQL Server uses BCP to move the data into a production table. With that solution I've successfully moved 5.2 billion rows into my production table with little to no backlog of BCP files. I have BCP running continually as a SQL Agent job.

My production table has the following schema:

CREATE TABLE [dbo].[SignalValues](
    [Timestamp] [datetime] NOT NULL,
    [SignalId] [uniqueidentifier] NOT NULL,
    [SignalValue] [nvarchar](4000) NULL,
    [SignalValueId] [uniqueidentifier] NOT NULL
)

Because of the volume of records, the table is partitioned on [Timestamp] every half hour from 2016-01-20 21:00:00.000 to 2016-06-15 08:00:00.000. (~11k total partitions).

My problem is twofold:

Querying the table in any way takes eons.
While queries are running against the table, the BCP processing basically stops. It's like all processing power goes to the query and I get a files backlog.

How do I speed up query performance? Users will mostly query based on [Timestamp] and SignalId. I created a non-clustered index on [Timestamp] but it didn't seem to speed things up that much. Seems to be that the more indexing I apply, the slower the BCP inserts which creates a file backlog. Any index suggestions?

My assumption of partitions is that internally they act as separate tables so if I were to query [Timestamp] for 2016-01-25 22:01:00.000, it should only lock the one partition it needs for the query and leave the other partitions unlocked. Is my assumption incorrect? It would seem so with the behavior seen in 2) above. How do I force SQL Server not to lock during a SELECT query (WITH (READPAST) and WITH (NOLOCK) didn't help)?

I should note that rows in this table will never be updated or deleted. Also, it's possible that data could be inserted from a time in the past, depending on what the applications providing my application with data set as the timestamp for their rows. This unfortunately negates any possibility of partition switching unless I were to do some crazy magic.

The average number of rows per partition is ~70 million.

Based on the feedback from my previous question (above), I was able to beef up my server considerably. The following is my server's system information:

Also, I've configured SQL Server as follows based on feedback:

All database-related files are connected to a SAN which is connected to the system via fibre channel
My database's data file is on its own drive alone -- a RAID 10 array of 4 spinning disks
tempdb consists of 6 files (half the number of logical processors), each sized at 512mb, all on one drive -- a RAID 0 array of 2 spinning disks
All transaction logs live on their own drive -- a RAID 1 array of 2 spinning disks.

The table does not have a clustered index. I'd had a clustered unique index (the PK) before but the uniqueness constraint caused inserts to take 10 minutes for 100k records in a table with 800m rows. My understanding was that a clustered index creates a uniqueness constraint internally per the heading, "Can you create a clustered index on a column with duplicate values?" from this page:

14 SQL Server Indexing Questions You Were Too Shy To Ask

I could be completely wrong though.

Let me know if I'm missing anything.

This is a long shot but seen as you are running SQL 2014 Enterprise I would have a test with using a Clustered Columnstore Index on this table and see if this improves performance for you.

Especially considering that you are using the table for selects and bulk inserts only - no updates and no deletes.

You will have the added advantage of taking a bit of load off the spinning disks and moving it more towards the CPU / Memory which you seem to have a decent amount of.


High availability SQL Server without Active Directory I'm fairly new to SQL Server. I'm looking to achieve a highly available SQL Server setup where if the primary database fails, the backup database takes over. What would you guys recommend?

I'm familiar with MySQL replication, however in the case of SQL Server it doesn't seem straight forward to switch the publisher role to a subscriber.

Ideally not a high availability cluster because I'd like to avoid Active Directory. I've had a brief search on google, however I'm asking on here to get the opinion of experienced pros.

Thanks in advance.

SIOS makes an excellent product that you can use without every supported version of SQL Server and is also SANless. It does block by block transfers and David Klee is a big enough fan that he even does sessions for them, and doesn't get remuneration for it. He just loves the product that much.

I'm not sure if it meets all of your needs but it's definitely worth checking out.

http://us.sios.com/


How to store 'n' days of web server logs in Sql Server? For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time.

We have a daemon that listens for request/response events from our load balancer and bulk inserts into the database.

However, we get around 1 GB of logs per day and we only need to keep about a week around (at least in this raw form).

What is the best way to store this data and the best way to delete old entries?

We've talked about storing each day's data in its own table, e.g. Log_2011_04_07 would have all the entries for that day, and then dropping the oldest table.  A view could be created to span all the day tables for easy querying. Is the feasible?

You should look in to partitioning.

http://technet.microsoft.com/en-us/library/dd578580%28SQL.100%29.aspx

The cool thing about partitioning is that you have just one table name (as opposed to the multiple table approach) so your insert statements remain static.  It works with every application - it's completely transparent to queries.  You don't have to worry about what happens if you end up with different indexes or statistics on each of the tables, either.

You create a partition function that decides how to break up the table into multiple tables behind the scene.  The function can only take one input parameter/field, and in your case, it would be a date field.  The function can break up the table by date, week, month, or year - in your case, you'd want date, 24-hour period.

Then build a SQL Server Agent job that uses T-SQL to swap out the last partition every day.  The delete becomes a metadata operation, and it's blazing fast.  Swap the partition, then drop the old one out.


Unable to access tables in sql server using oracle dblink I have created a Heterogeneous  connection between oracle and sql server. and tested with below query,

SELECT * FROM sys.tables@SQLSERVER;

Then I have created a new Database in SQL server called "TESTCONNECTION" and created a table called "dbo.TEST". After that I changed the default database of DSN to "TESTCONNECTION". But below query fail with following error message.

SELECT * FROM dbo.TEST@SQLSERVER;

  [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
  'DBO.TEST'. {42S02,NativeErr = 208}[Microsoft][ODBC SQL Server
  Driver][SQL Server]Statement(s) could not be prepared.
  {42000,NativeErr = 8180

}

How to overcome this error and access tables created under "TESTCONNECTION".

One potential cause could be that the default database listed for the user account being used to connect to SQL Server is pointed to the wrong database.  Adjust this on the SQL Server side and see if that resolves your issue.


Mysql Not Null Columns accepting null values I am having trouble with Mysql not null columns. It seems my mysql installation is accepting null values for NOT NULL columns.

My mysql version is 5.6.25-1~dotdeb+7.1(debian).

Take this table for instance:

CREATE TABLE `cities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `state_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5562;

When I insert a value like this:

insert into cities(state_id) values (20);

Mysql spills a warning but commits the value anyway. Here is the warning.

12:51:43    insert into cities(state_id) values (20)    1 row(s) affected, 1 warning(s): 1364 Field 'name' doesn't have a default value 0.000 sec

If I remove the Unique Key unique_city_in_state I get the same behaviour.

I also tried creating the name column with a DEFAULT NULL, like so:

  `name` varchar(255) NOT NULL DEFAULT NULL,

This spills a error that would roughly translate to (Default value invalid for name).

I tried on a different mysql installation 5.1.73-1 (Debian) and I have the same behavior.

Now if I do this

insert into cities (name, state_id) values (null, 19);

I get the ERROR 1048 (23000) meaning the column name cannot be empty.

Any help is welcome.

It actually doesn't accept NULL values it considers it as empty string. That's because you have your server in non-strict mode. That controls how MySQL handles invalid or missing values in inserts and updates. You can read more about modes here: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

mysql> insert into cities(state_id) values (20);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+

mysql> select name is null from cities where id = LAST_INSERT_ID();
+--------------+
| name is null |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into cities(state_id) values (20);
ERROR 1364 (HY000): Field 'name' doesn't have a default value


MongoDB: Privileges on objects created by me only? Is it possible to define a custom role, or otherwise assign privileges to allow a user read/write access only on objects that they have created?

Imagine 3 applications, each having its own login. For example: The recipe_app uses recipe_user to access the MongoDB store, and the lizard_app uses lizard_user to do the same. Part of the way the applications work is that they create and drop their own databases as necessary.

At any given time there may be only a northern_recipe database, a southern_recipe database, and a western_lizard database, or only an eastern_recipe database and a northern_lizard database (or any other permutation), and what will exist when is not foreknown.

Is it possible to set up recipe_user's privileges to only be able to read/write the recipe_* databases? Let's assume that the naming conventions stay as consistent as those in the example.

I thought about adding a tag with the creating user's name to each collection on import, and then a "redaction" filter to limit its access, but I don't like

that what I want to filter on is properly metadata, and shouldn't be mixed in with the data, and
that it's not really addressing the issue at the database level.

UPDATE: Inspired by this question about dropping collections based on a regex, I'm thinking running an admin script like this on a regular basis would achieve what I want, but with some delay between a database's creation and effecting the grant. (Please note this is tested but makes no presumption of being optimized.)

db.runCommand({listDatabases:1})['databases'].forEach(
  function(d) {
    if (d.name.match(/lizard$/)) {
      db.grantRolesToUser(
        "lizard_user",
        [ {role: "readWrite", db: d.name} ]
      )
    }
  }
)

MongoDB 3.4.0

A database does not need to exist at the time you grant a user permissions on it.

So the solution to this is simply to pre-grant a superset of all permissions you expect might be needed in the future. Then they will just sit there unless and until needed.

In my case the list of permissions needed changes fairly frequently, but the prefix-style patterns are predictable. (Namely, the user's name matches the first part of its databases' names.) So I use a config file like this, to which I add entries before they are needed. The script runs regularly, and grants new permissions, which just sit there until they are actually used.

[ { 'replica_set' : 'cluster0',
    'members' : [ 'server0', 'server1', ... 'serverN' ],
    'permissions' : [ 'user' : 'lizard_user', 'db_patterns' : [ 'lizard_' ] },
    ...
  },
  { 'replica_set' : 'cluster1',
  ...
  }
]

I'm posting this workaround primarily to note the misconception I had about granting permissions on not-yet-existent objects, in case others share it. It would still be cool if there were a privilege like 'readWriteAllMyDatabases'.


IN Clause - performance difference between listing values and query I changed this query

SELECT ...
FROM linkedServer.DB.Schema.Table1 t1
LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID
WHERE t1.BRANCH_ID NOT IN (
        '009991', '009992', '009993', '009994', '009995', '009996', '009999', '900001',
        '900002', '900003', '900004', '900005', '900006', '900007', '900008', '999991',
        '999992', '999993', '999994', '999995'
        )
GROUP BY ...

into this

SELECT ...
FROM linkedServer.DB.Schema.Table1 t1
LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID
WHERE t1.BRANCH_ID NOT IN (
        SELECT b.BRANCH_ID
        FROM TB_BRANCH b --25 rows in total
        WHERE b.START_DT = '99999999' --the result of this sub-query is fewer than list above.
        )
GROUP BY ...

then performance dropped.

Why is this huge change of Execution plan happening? How can I avoid performance drop while using a sub-query in IN clause?

You are using a linked server to access Table1 and Table2.

The first query is sent as is to the other server and executed there returning only the rows you want.

The second query is doing a join between a local table TB_BRANCH  and a remote table Table1. To do that it fetches all rows from Table1 and all rows from Table2 to your local server and does the join operations locally.

The drop in performance is there because it takes time to move the entire contents of Table1 and Table2 from the remote server to the local server.

  How can I avoid performance drop while using a sub-query in IN clause?

Move the content of TB_BRANCH (25 rows) to the remote server to avoid doing a join between tables on different servers.

One way of moving the rows from TB_BRANCH to the remote server is to execute the query on the remote server using sp_executesql with the values as a parameter in a XML structure. Unpack the XML to a table variable and use the table variable in your main query.

declare @X varchar(max);
set @X = (
         select b.BRANCH_ID
         from dbo.TB_BRANCH as b
         where b.START_DT = '99999999'
         for xml path('')
         );

-- Create a SQL Statement
declare @SQL nvarchar(max);
set @SQL = '
declare @B table(BRANCH_ID varchar(10) primary key);

insert into @B(BRANCH_ID)
select B.X.value(''.'', ''varchar(10)'')
from (select cast(@X as xml)) as T(X)
  cross apply T.X.nodes(''/BRANCH_ID/text()'') as B(X);

select *
from Schema.Table1 as t1
  left outer join Schema.Table2 as t2
    on t1.ORDER_ID = T2.ORDER_ID
where t1.BRANCH_ID not in (
                          select B.BRANCH_ID
                          from @B as B
                          );';

exec linkedServer.DB.Schema.sp_executesql @SQL, N'@X varchar(max)', @X

I have guessed the data type of BRANCH_ID to be varchar(10) you need to modify that in two places in the script if it is something else.

The XML parameter has to be a varchar(max) instead of as XML since the XML datatype is not supported with remote queries. Table Valued Parameters is not supported either so that is not an option here.

The query plan of this query is not very interesting. It will just be a Remote Scan. If you want to know what the plan looks like on the remote side you have to capture the plan there.


Parallel inserts cause deadlock on DB2 The scenario is the following: The application attempts to insert into the same table from two parallel threads making 4000 insertions in each thread (separate transaction per thread). It causes the DB always to fail with the following exception:

com.ibm.db2.jcc.a.pn: The current transaction has been rolled back because of a deadlock or timeout. Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.52.95

The full log is ([#1] indicates the 1st thread/transaction, [#2] is correspondingly the 2nd):

SQL: create table line (id integer generated by default as identity, line_number integer not null, constraint line_pk primary key (id))
[#1] SQL: insert into line (line_number) values (1)
[#1] SQL: insert into line (line_number) values (2)
[#2] SQL: insert into line (line_number) values (1)
[#2] SQL: insert into line (line_number) values (2)
[#1] SQL: insert into line (line_number) values (3)
[#2] SQL: insert into line (line_number) values (3)
[#1] SQL: insert into line (line_number) values (4)
[#2] SQL: insert into line (line_number) values (4)
...
[#2] SQL: insert into line (line_number) values (1608)
[#1] SQL: insert into line (line_number) values (1608)
[#2] SQL: insert into line (line_number) values (1609)
[#2] SQL: insert into line (line_number) values (1610)
[#2] SQL: insert into line (line_number) values (1611)
...
[#2] SQL: insert into line (line_number) values (1654)
[#2] SQL: insert into line (line_number) values (1655)
[#1] [org.epo.lifesciences.slice.db.DBTest] Thread #1 has failed
org.springframework.dao.DeadlockLoserDataAccessException: StatementCallback; SQL [insert into line (line_number) values (1608)]; The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.52.95; nested exception is com.ibm.db2.jcc.a.pn: The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.52.95
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTransl
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLException
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:519)
     ...
[#2] SQL: insert into line (line_number) values (1656)
[#2] SQL: insert into line (line_number) values (1657)
...
[#2] SQL: insert into line (line_number) values (3999)
[#2] SQL: insert into line (line_number) values (4000)
[#2] Thread #2 completed

It looks like that lock space is exhausted much earlier then transaction log is exhausted. Solutions which I see (and seem to work):

Increase lock space, in particular trick the parameters:

locklists – maximum storage for lock list configuration parameter
maxlocks – maximum percent of lock list before escalation configuration parameter

Commit more frequently.

Both of them are not strictly acceptable because:

One need to know in advance what should be the size of locklists which needs to be increased with the growing number of insertions. Thus DB2 cannot automatically adapt to increasing workload efficiently. Also DB instance needs to be restarted when this parameter is changed.
Often commits also mean that there should be a way to roll back all previous commits if something goes wrong at certain point of time. That complicates the application as it needs to implement "revert" logic (savepoints won't help). Moreover intermediate commits will be visible to data requester, so application needs also to track such "uncompleted" tasks and hide the data from client.

The test application works OK both for MySQL, HSQL and MSSQL as is (with no further tricks). So I believe there should be a way to make it working on DB2 without code refactoring and staying on SQL'92 compliant level. Is it possible with enterprise-level DB2? Perhaps I miss something trivial, any feedback is welcomed.

Also check the LOCKTIMEOUT, DLCHKTIME, and LOCKSIZE parameters. This article from DBI has been helpful to know what to set them to. It slants toward warehousing, but does mention settings for OLTP as well.


What is the proper way to design this database? So, I'm not a database expert (or really have much experience at all other than my college classes) and I need someone to point me in the right direction on how to design this problem. To be very clear, I'm out of school and I have my degree. This is NOT a homework problem.

I work as a software engineer at a company that manages other company's warehouses. I built a system that tracks our associate's productivity by the hour and I need the information to be stored in a database. Previously I have used Java and simply serialized classes and stored the data in files on our server but the problem is I've had to change so many data types to account for more information we wish to track and I lose the serialized objects after any significant changes. Anyways, I decided to just use a database to store this stuff but really am having a hard time with it's design.

So the information I need stored is as follows...

Associates
Jobs (Associates can work multiple jobs throughout the day and each job has a different goal average they need to reach)
Each Associates' Total Work Hours for a given day
Need to save all this for each separate day (this is the hardest part for me and I have a mental block on how to separate all that by day)

A little more info: The way I designed this in code was like this.

HashMap<LocalDate, HashMap<Employee, DayData>> data = new HashMap<>();

So as you can see each day has a list of employees each with a "DayData" object associated with them.

The DayData object contains total day work hours and a list of WorkJob.

WorkJob stores the name of the job, the unit per hour the associate hit, and the units the employee packaged.

I hope this is clear as mud. I can explain anything further if needed. I really need someone to point me in the right direction with this. Please and thank you.

You're structuring your data using nested structures. Instead, you should look for relations (tables), with a flat structure... and ask the database to join them when you need content from more than one. Don't be afraid to JOIN them all, this is what RDBMS are for.

You could use the following structure. I've defined it using textual SQL; but you can work out the equivalent ERD if you need to, based on all the foreign keys (REFERENCES or FOREIGN KEY) already defined in the tables.

Table where relevant data for your associates

CREATE TABLE associates
(
    associate_id INTEGER NOT NULL PRIMARY KEY,  /* Probably should be IDENTITY */
    associate_name TEXT NOT NULL,
    other_data TEXT
) ;

Table where all jobs are described:

CREATE TABLE jobs
(
    job_id INTEGER NOT NULL PRIMARY KEY,  /* Probably should be IDENTITY */
    job_description TEXT
) ;

Table where you assign which are the job/s that every associate should perform, and the average_daily_goal:

CREATE TABLE jobs_x_associates
(
    job_id INTEGER NOT NULL REFERENCES jobs(job_id),
    associate_id INTEGER NOT NULL REFERENCES associates(associate_id),
    average_daily_goal DECIMAL(5,2) NOT NULL,

    -- (job_id, associate_id) is the natural primary key
    PRIMARY KEY(job_id, associate_id)
) ;

Table where you store the worked hours that every associate performed of every kind of job s/he is assigned to perform (this is, more-or-less, equivalent to your DayData, but with a column for associate_id, so that it can relate to it):

CREATE TABLE worked_hours
(
    job_id INTEGER NOT NULL REFERENCES jobs(job_id),
    associate_id INTEGER NOT NULL REFERENCES associates(associate_id),
    work_day date NOT NULL,
    worked_hours DECIMAL(5,2) NOT NULL,

    -- The pair (job_id, associate_id) must exist in jobs_x_associates
    -- we do not allow associates to perform jobs they have been not
    -- assigned. Depending on business rules, you can lift this
    -- restriction
    FOREIGN KEY (job_id, associate_id)
        REFERENCES jobs_x_associates(job_id, associate_id),

    -- Only one entry per associate,job and day
    PRIMARY KEY(associate_id, job_id, work_day)
) ;

Sample data for base tables:

INSERT INTO associates
    (associate_id, associate_name, other_data)
VALUES
    (1, 'Alice Cooper', 'nice buy'),
    (2, 'Bob Geldorf', 'difficult to define') ;

INSERT INTO jobs
    (job_id, job_description)
VALUES
    (101, 'Cleaning windows'),
    (102, 'Dish washing') ;

Sample data for jobs assigned and worked hours:

-- What should everyone do?
INSERT INTO jobs_x_associates
    (job_id, associate_id, average_daily_goal)
VALUES
    (101, 1, 5.0), -- Alice should clean windows for avg. 5 h/day
    (101, 2, 3.0), -- Bob should clean windows for avg. 3 h/day
    (102, 1, 1.0), -- Alice should wash dishes for avg. 1 h/day
    (102, 2, 0.5); -- Bob should wash dishes for avg. 0.5 h/day

-- We record what they actually did
INSERT INTO worked_hours
    (job_id, associate_id, work_day, worked_hours)
VALUES
    (101, 1, '2017-07-01', 5.0),
    (101, 2, '2017-07-01', 3.0),
    (102, 1, '2017-07-01', 0.7),
    (102, 2, '2017-07-01', 1.0),

    (101, 1, '2017-07-02', 3.0),
    (101, 2, '2017-07-02', 2.0),
    (102, 1, '2017-07-02', 0.8),
    (102, 2, '2017-07-02', 1.3) ;

With these data, you can perform queries such as:

 SELECT
     work_day, associate_name, job_description,
     average_daily_goal, worked_hours
 FROM
     worked_hours
     JOIN jobs USING(job_id)
     JOIN associates USING (associate_id)
     /* LEFT */ JOIN jobs_x_associates USING (job_id, associate_id)
 ORDER BY
     work_day, associate_name, job_description ;

 work_day   | associate_name | job_description  | average_daily_goal | worked_hours
 :--------- | :------------- | :--------------- | -----------------: | -----------:
 2017-07-01 | Alice Cooper   | Cleaning windows |               5.00 |         5.00
 2017-07-01 | Alice Cooper   | Dish washing     |               1.00 |         0.70
 2017-07-01 | Bob Geldorf    | Cleaning windows |               3.00 |         3.00
 2017-07-01 | Bob Geldorf    | Dish washing     |               0.50 |         1.00
 2017-07-02 | Alice Cooper   | Cleaning windows |               5.00 |         3.00
 2017-07-02 | Alice Cooper   | Dish washing     |               1.00 |         0.80
 2017-07-02 | Bob Geldorf    | Cleaning windows |               3.00 |         2.00
 2017-07-02 | Bob Geldorf    | Dish washing     |               0.50 |         1.30

 SELECT
     associate_name, job_description,
     average_daily_goal,
     CAST(avg(worked_hours) AS DECIMAL(10,2)) AS average_worked_hours
 FROM
     worked_hours
     JOIN jobs USING(job_id)
     JOIN associates USING (associate_id)
     /* LEFT */ JOIN jobs_x_associates USING (job_id, associate_id)
 GROUP BY
     associate_name, job_description, average_daily_goal
 ORDER BY
     associate_name, job_description ;

 associate_name | job_description  | average_daily_goal | average_worked_hours
 :------------- | :--------------- | -----------------: | -------------------:
 Alice Cooper   | Cleaning windows |               5.00 |                 4.00
 Alice Cooper   | Dish washing     |               1.00 |                 0.75
 Bob Geldorf    | Cleaning windows |               3.00 |                 2.50
 Bob Geldorf    | Dish washing     |               0.50 |                 1.15

You can check everything at dbfiddle here

NOTE: If you do allow associates to perform (and record) jobs they were not assigned to do, remove the FOREIGN KEY mentioned before, and change the /* LEFT */ JOIN to actual LEFT JOIN. This will allow the database to still show them, even if the average_daily_goal is N/A (i.e.: NULL).