Connection Issues with Informix OLEDB Provider I've been trying to establish a OLEDB Connection to an Informix Dynamic Server. (Version IBM Informix Dynamic Server Version 12.10.FC12WE)

Background Info:
A program, which will be used by my company, requires OLEDB and doesn't support ODBC. The colleague,who is currently tasked with managing the implementation, has a lot of other work piled up and so the implementation will be delayed. In the meantime, I've been tasked to figure out how to properly configure the Windows 10 Client PC (which will run the program) for the connection. I have very little experience with databases, so I've searched for about 2 weeks for answers in various documentations and forums. So far, I had mixed success.

What has already been done:

The coledbp.sql- script has been run against the sysmaster table on the database
Informix Client SDK and Microsoft Data Access Components have been installed on the Client PC.
Using setnet32 the registry entries for host:port have been set.
The driver ifxoledbc has been registered.
ODBC-Datasources (both 64-Bit and 32-Bit) have been created and tested (they work fine).

Various Tests

For all my tests I've been using the Connection String:

  "Provider=ifxoledbc;Data Source=mydatabase@myserver;User ID=myuser;Password=mypassword;

First I tried to test the connection with an .udl-File. I select the IBM Informix OLE DB Provider and enter the data source and userdata. When I test the connection I get an Error like:

  "Initialisation of the provider failed. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

From what I could find, there are many possible reasons for this error, and I don't know if can get a more specific error message somehow.

With the Microsoft OLE DB Provider for ODBC Drivers and the ODBC-datasource for the Server the test connection worked.

In Microsoft Excel (32-Bit) I also tried both the first mentioned Connection String and the Connection String, which is built when entering the infos in the .udl settings. Same Error with both cases.

For my next test I used a .vbs - script, which was presented in the IBM Informix Developers Handbook:

On Error Resume Next

set conn=createobject("ADODB.Connection")

conn.connectionstring = "Provider=ifxoledbc;DataSource=mydatabase@myserver;UserID=myuser;Password=mypassword;"

conn.open

If Err Then
   WScript.Echo "Error!! Open"+conn.Errors(0).Description
Else
   WScript.Echo "Connected"
   conn.close
end if

This results once again results in the "multi-step"-Error.

The last thing I tried was using the Rowset Viewer of MDAC. I used the exact same input as with the .udl - File. But to my surprise it managed to connect! (It returned the error "Unknown Interface", but still worked) It correctly read the entries I set with the setnet32-Tool. (I checked this by temporarily removing one of the Entries, which resulted in an additional error and no connection)

Now I have a few questions:

Is there anything additional to configure on the Client PC?
How does the Viewer succeed, where every other test fails?
Is there any way to get a more accurate error code?
Are there major flaws in my tackling of the problem?

I appreciate every suggestion anybody can give me.

So, after giving up searching for answers, I started trying every imaginable combination of properties with different registries set and not set and I managed to get it working.

The Connection String was missing UNICODE=TRUE.

I can't say for sure, if I already tried this before and the error originally came from incorrectly set registry entries or if that simple solution slipped past my testing.

Anyways here is what I did, in addition to what was mentioned in my original post (no idea if that made any difference):

registered the ifxoledbc.dll (32Bit and 64Bit) with the respective regsvr32.exe (Originally I used the 64-Bit regsvr32.exe for both)
Added registry entries in Computer\HKEY_CLASSES_ROOT\Ifxoledbc\CLSID (64Bit) and Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Ifxoledbc\CLSID (32Bit) with name: OLEDB_SERVICES type: REG_DWORD and value: 0xffffffff

Concluding:

I'm still surprised, that the RowsetViewer somehow bypassed the need for the UNICODE property. Maybe it automatically sets it to true?

The interface:unknown error I mentioned with the viewer seems to have been fixed in a newer version of the Informix Client SDK (Only happened with 64-Bit Operating Systems using 32Bit Tools).

During my successful testing I also noticed, that Excel seemingly didn't need the %INFORMIXDIR% environment variable. FYI, when I was testing the ODBC datasources i had to edit the %INFORMIXDIR" to either point to my 32Bit or 64Bit folder (Not doing so resulted in a unable to load shared library error). The 64Bit datasource was also incompatible with my 32Bit Excel. With the OLEDB Provider however, it connects just fine with %INFORMIXDIR% still pointing to the 64Bit folder.


Identify cached data for a specific query in data cache I'm trying to identify, if possible, how long it takes for data being cached by a stored procedure to be taken out of the cache.

I know there is the PLE counter in sys.dm_os_performance_counters, but I think in my scenario that won't help me very much.

Disclaimer, all of this was handed down to me to improve:

I have a stored procedure which returnes about 250 Mb of data into the cache, when being ran by one user. The procedure returns user-specific information, so the procedure is called with a @userID input parameter.

From the data returned by this stored procedure (about 3000 rows) it displays row-specific information, for one row at a time. There is a functionality that when a user presses a "Next" button in the application UI it goes and gets the row-specific information for the next row in the result and so on.

Due to the data already being in cache, when testing with a single user the response time was ~1 second, because all of the 250 Mb would fit in cache without any problem and not being cleared very fast.

I've tested the procedure with a cold cache and the execution time was about 10 seconds to return the data.

Now, I have about 30 users running the stored procedure in the same time interval and they have reported experiencing about a 10 second delay when pressing the "Next" button.

Surprisingly this is the same exec. time when pushing data into the cache and because there are 30 users, my conclusion at this point is because the total amount of data being pushed into cache is about 7Gb (total server memory is 16Gb), the data gets pushed in and out of cache very often.

This is all just a theory at this point, but I'm trying to identify some metrics that directly point at this being the issue. I know PLE could be an indicator, but is there anything that could be more specific, for a query for example.

My recommendation would be to re-write the stored procedure in a more efficient way, but that would take lots of time and business is not very interested in spending time for that. If I can prove, with this theory of mine, that there is no way, then they might allow for a re-write of the procedure.

Oh, and crazy enough, they're not very happy about increasing server memory either. Talking about not giving me options..

If the goal of your question is to determine if your stored procedure is having to do physical reads to satisfy the query (instead of retrieving the data from memory), you might be able to use the following query in the stored procedure to capture the physical reads during execution and possibly save that information for auditing purposes

declare @start_reads bigint
select @start_reads = reads from sys.dm_exec_requests where session_id = @@spid

-- Your query here

select reads - @start_reads from sys.dm_exec_requests where session_id = @@spid


Problem deleting detached partition in DB2 LUW V9.7 Recently in prod environment we are having problems while dropping the detached partitions its giving this error

  """ DB21034E The command was processed as an SQL statement because it
  was not a valid Command Line Processor command. During SQL processing
  it returned: SQL20285N The statement or command is not allowed because
  the table named "schemaname.tablename_JAN1" has detached dependents or
  the asynchronous partition detach operation is not complete. Reason
  code = "2". SQLSTATE=55057"""

I did some research and could find this from IBM saying we need to restart the instance to solve the issue. Link below I think you could have seen many of such incidents of what i described, please help me with this issue thanks in advance

http://www-01.ibm.com/support/docview.wss?uid=swg21515721

When you detach a partition from a table, the detach operation returns quickly, but DB2 then starts a background process to clean up any global indexes.  You can't drop the table containing the detached partition until this process completes.

So, have you verified that the asynchronous cleanup that occurs after you issue the ALTER TABLE ... DETACH is actually finished?


What is a good guideline for sampling for statistics updates Scope: A billion record table that is indexed in 10 ways.  Rate of change per day is 1/2 a percent.

Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most likely hit the tail end.  I assume statistics need updating frequently for this type, otherwise the recent data becomes unrepresented in the index?

Other indexes are more randomly distributed, e.g. (Customer Key, Datetime).  These can do with less frequent updates, since the statistics are quite representative of the whole.  We can let the index change enough to force auto statistics updates on these, correct?

For both types, is there any benefit from increasing the sampling from 10% to 100%, if the data to be sampled is random and quite representative of the whole?

Looking for best practice with TB data.

If you always query the tail, you can give a shot to filtered indexes. Index 10 ways with, say,  WHERE datetimecolumn > @INDEXBUILDDAY-30. Periodically (daily, weekly?) you can move this filter forward by adding (online) a new set of filterd indexes and then dropping the old ones. With a much smaller size and a much more recent build (implying recent stats), they would be 'fresh' and very appetizing for the optimizer. The periodic rebuild doesn't even need to scan the base table (no huge IO, no BP cache pollution), the old filtered index should always be usable as a source to build the next filtered index (it covers it).

For those queries that land outside the covered filtered tail, you can keep some (less than the 10 ways hopefully) full, unfiltered indexes. With the right voodoo invocation the optimizer might get your point and use the filtered ones when appropriate and only resort to the full ones when necessary...


Help solving a Clustering Issue: [sqsrvres] ODBC sqldriverconnect failed I have a 2 node cluster (NODE-A & NODE-B) with 2 SQL instances spread between them. INST1 prefers NODE-A, INST2 prefers NODE-B. INST1 started generating errors then, failed over to NODE-B. Migrating INST1 back to NODE-A generates the connection errors after it logs a "Recovery is complete." message.

Win 2008 R2 Ent.
SQL 2008 R2 Ent.

Errors from the Event Log after first failure:

[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
[sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Query timeout expired
[sqsrvres] OnlineThread: QP is not online.
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]The connection is no longer usable because the server failed to respond to a command cancellation for a previously executed statement in a timely manner. Possible causes include application deadlocks or the server being overloaded. Open a new connection and re-try the operation.
[sqsrvres] ODBC sqldriverconnect failed
[sqsrvres] checkODBCConnectError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

Please check if changing the time-out value for the SQL Server Resource in the Cluster Administrator helps.


Decode query truncates a date I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database

SELECT
    DECODE(
        TO_CHAR(MIN(DATE), 'YYYYMMDD')
        , ''
        , NULL
        , TO_DATE(TO_CHAR(MIN(DATE), 'YYYYMMDD'), 'YYYYMMDD')
    )
FROM TABLE
WHERE id = 76423

which returns 02/03/29. The year should be 1929 and, in fact, that is what I get if I do

SELECT TO_DATE(TO_CHAR(MIN(DATE), 'YYYYMMDD'), 'YYYYMMDD')
FROM TABLE
WHERE id = 76423

I don't understand why this should happen, as the date format is clearly specified.  Can someone shed some light on this?  This is a problem since the date returned by the DECODE query gets read as 02/03/2029 when it is used.

EDIT: The two conversions (from date to char and then again to date) here is due to the fact that the query as I put it here is really two different queries: the first one inserts a DATE in an auxiliary table's column which has a CHAR format; then another query takes this data to the final table, where it's stored as a DATE, and hence the second conversion.  I simplified it here to make it more understandable. The DECODE is necessary as some of the rows don't have data on the date column.

Anyway, I think the best solution is to do ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD' as suggested by @Phil and @LeighRiffel, and then both queries (with and without DECODE) return the same date.  Another possible workaround would be to leave the final data as CHAR instead of converting it to DATE, but this would have a large impact on the application I'm working on, so I'm sticking to the ALTER SESSION ... way.

As a_horse indicated, select min(date)... is sufficient.  The DECODE and multiple conversions just slow things down without accomplishing anything.  Even as parts of queries, you should eliminate this needless processing.

As Phil indicated, the displaying of dates is controlled by the NLS_DATE_FORMAT parameter and can be altered to display dates as you wish.  This does change how the queries run (unless there are missing format specifications or SQL injection is being done).  Change the display format like this:

 ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD';

When doing a to_date on a two digit year, use YY when you want the first two digits of the year to match the current year, but use RR when you want the first two digits to be 20 for years ending in 0-49 and 19 for years ending in 50-99.  See the SQL Language Reference for more information:

  The RR datetime format element is similar to the YY datetime format
  element, but it provides additional flexibility for storing date
  values in other centuries. The RR datetime format element lets you
  store 20th century dates in the 21st century by specifying only the
  last two digits of the year.

  If you use the TO_DATE function with the YY datetime format element,
  then the year returned always has the same first 2 digits as the
  current year. If you use the RR datetime format element instead, then
  the century of the return value varies according to the specified
  two-digit year and the last two digits of the current year.

  That is:

  If the specified two-digit year is 00 to 49, then

  If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
  If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2
  digits of the current year.

  If the specified two-digit year is 50 to 99, then

  If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2
  digits of the current year.
  If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

  The following examples demonstrate the behavior of the RR datetime
  format element.

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL;
Year
----
1998

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL;
Year
----
2017


MySql: #1221 - Incorrect usage of UNION and ORDER BY I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts.

SELECT * FROM comment AS tbl WHERE sscm_id IN
(
  SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4
    ORDER BY t1.sscm_id DESC LIMIT 3
  UNION  ALL
  SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3
    ORDER BY t2.sscm_id DESC LIMIT 3
)

which gives me the following error:

1221 - Incorrect usage of UNION and ORDER BY

Some searching & googling seemed to suggest that placing () around each subquery might solve this problem but changing the query to

SELECT * FROM comment AS tbl WHERE sscm_id IN
(
  (SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4
     ORDER BY t1.sscm_id DESC LIMIT 3)
  UNION  ALL
  (SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3
     ORDER BY t2.sscm_id DESC LIMIT 3)
)

but this gives me the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL (SELECT sscm_id FROM bpsocial_comment AS t2 WHERE t2.sscm_oid = 3 OR' at line 4

So: how can I get the UNION in combination with the ORDER BY in the subqueries to work?

Thanks
R

There are several ways to pass the IN / LIMIT limitation and the UNION / ORDER BY syntax requirements.

Wrap the Union in a derived table:

SELECT * FROM comment AS tbl WHERE sscm_id IN
  ( SELECT sscm_id
    FROM
      ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4
          ORDER BY t1.sscm_id DESC LIMIT 3)
      UNION  ALL
        ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3
          ORDER BY t2.sscm_id DESC LIMIT 3)
      ) AS tmp
  ) ;

Use JOIN instead of WHERE / IN:
UNION is required here instead of UNION ALL to avoid duplicates

SELECT tbl.*
FROM comment AS tbl
    JOIN
        ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4
            ORDER BY t1.sscm_id DESC LIMIT 3)
        UNION
          ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3
            ORDER BY t2.sscm_id DESC LIMIT 3)
        ) AS tmp
      ON tmp.sscm_id = tbl.sscm_id ;


Why Postgres replication write_lag is measured in seconds I'm running pg_receivewal locally, without compression, it stores segments on the same drive with the db files. Here's the replication statistic:

imbolc=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 12920
usesysid         | 10
usename          | postgres
application_name | pg_receivewal
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2019-08-25 13:18:00.504531+07
backend_xmin     |
state            | streaming
sent_lsn         | C/80009218
write_lsn        | C/80009218
flush_lsn        |
replay_lsn       |
write_lag        | 00:00:09.838314
flush_lag        | 00:00:40.028949
replay_lag       | 00:00:40.028949
sync_priority    | 0
sync_state       | async

If I set synchronous_commit = off, write_lag still remains significant, above 2.5 seconds.

So my questions are:

why the write_lag is so high?
does it mean that in case of the db crash last 10 seconds of transactions can be lost?
is there a way to improve it?

does it mean that in case of the db crash last 10 seconds of transactions can be lost?

If the database has a "soft" crash, like power failure, it will go through autorecovery upon startup, and will recover all transactions (other than those possibly lost to synchronous_commit = off) using the log files it found in the pg_wal or pg_xlog directory.  pg_receivewal is irrelevant to this situation.

If the database has a "hard" crash, such that the storage media is permanently lost and you have to recovery from a backup, then you will lose much more than 10 seconds of transactions.  Since pg_receivewal is storing to the files in the same place as the db files, they will all be lost together.  Running pg_receivewal on the same machine as the database and storing the files to the same drive is a pointless exercise, suitable only for testing purposes.

Other than that issue, you are misinterpreting what the field is for.  It is not to measure the data at risk, it is "consistent with the goal of measuring synchronous commit and transaction visibility delays for recent write transactions."


SPARSE Column in SQL Server I have just learned about SPARSE columns in SQL Server.  I have never used them; I've just read about it on the Internet.

Can I ALTER an existing Nullable column in large transactional tables to take advantage of the SPARSE property? Would it be beneficial for space and performance?

You could test space-reduction using something like:

USE tempdb;
CREATE TABLE xc
(
    x INT NULL
);

INSERT INTO xc (x) VALUES (CASE WHEN (RAND() * 10) < 5 THEN NULL ELSE (RAND() * 10) END);
GO 10000

SELECT o.name, SUM(au.used_pages) * 8192 / 1048576e0
FROM sys.allocation_units au
    INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
    INNER JOIN sys.objects o on o.object_id = o.object_id
WHERE o.name = 'xc' AND (au.type = 1 or au.type = 3)
GROUP BY o.name;

SELECT * FROM dbo.xc

ALTER TABLE xc ALTER COLUMN x INT SPARSE NULL;
ALTER TABLE xc REBUILD;

SELECT o.name, SUM(au.used_pages) * 8192 / 1048576e0
FROM sys.allocation_units au
    INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
    INNER JOIN sys.objects o on o.object_id = o.object_id
WHERE o.name = 'xc' AND (au.type = 1 or au.type = 3)
GROUP BY o.name;

Running this drops the number of pages used by the table.


MongoDB performance problem I have a MongoDB for storing result like this

{_id, type, imei, lat, lng, spd, dir, time}
{_id, type, imei, lac, cid, time}

If type is f first one is used and if it is l second one is used.

Here is the indexes:

"indexSizes" : {
    "_id_" : 340358704,
    "imei_1" : 614508160,
    "type_1" : 183616608,
    "type_1_imei_1" : 349793808
},

Collection has about 10 million documents. I have many queries on type and imei, and for now only types of f are used in applications.

This is a line from log file

Sun Aug 31 19:14:39.402 [conn782250] query xxyyxx.locs query: { $query: { $and: [ { imei: "359710042581612" }, { type: "f" } ] }, $orderby: { time: -1 } } ntoreturn:1 ntoskip:0 nscanned:140455 scanAndOrder:1 keyUpdates:0 numYields: 59 locks(micros) r:38456361 nreturned:1 reslen:159 19727ms

This took about 20 seconds, too much.

I need to optimize my collection more. This is used in a web application and sometimes queries like this are performed for 10 imeis, taking about 200 seconds, which may lead to a timeout. This is performed async (ajax) but still too much waiting time.

Any help from experts here is more than welcome.

Also, if you think I missed anything, please do request more information.

Thanks in advance

BTW, I'm using PHP for web part (Laravel framework and https://github.com/jenssegers/Laravel-MongoDB)

EDIT: Here is the explain (REMOVED)

EDIT2: After creating better indexes:

MongoDB shell version: 2.4.9
connecting to: test
> use gpstracker
switched to db gpstracker
> db.locs.stats()
{
    "ns" : "gpstracker.locs",
    "count" : 10137188,
    "size" : 1400944384,
    "avgObjSize" : 138.19852053646434,
    "storageSize" : 1580060672,
    "numExtents" : 19,
    "nindexes" : 2,
    "lastExtentSize" : 415145984,
    "paddingFactor" : 1,
    "systemFlags" : 0,
    "userFlags" : 0,
    "totalIndexSize" : 826806176,
    "indexSizes" : {
        "_id_" : 359997456,
        "type_1_imei_1_time_-1" : 466808720
    },
    "ok" : 1
}
> db.locs.find({ $and: [ { imei: "359710042581612" }, { type: "f" } ] }).explain()
{
    "cursor" : "BtreeCursor type_1_imei_1_time_-1",
    "isMultiKey" : false,
    "n" : 150158,
    "nscannedObjects" : 150158,
    "nscanned" : 150158,
    "nscannedObjectsAllPlans" : 150158,
    "nscannedAllPlans" : 150158,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 329,
    "nChunkSkips" : 0,
    "millis" : 128173,
    "indexBounds" : {
        "type" : [
            [
                "f",
                "f"
            ]
        ],
        "imei" : [
            [
                "359710042581612",
                "359710042581612"
            ]
        ],
        "time" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ]
    },
    "server" : "CDSX001:27017"
}

Your query is using the index to fetch the results but needs to sort them afterwards which is an expensive operation.

"scanAndOrder" : true, scanAndOrder is a boolean that is true when the query cannot use the order of documents in the index for returning sorted results: MongoDB must sort the documents after it receives the documents from a cursor.

The solution is to apply an index on {imei:1, type:1, time:-1} or {type:1, imei:1, time:-1} (the imei,type order on the index depends on imei and type selectivity)


Documentation for selecting table name returning csv of the row It seems that in Postgres 9.2 this syntax

SELECT <table name>
FROM <table name>

Returns rows with all columns aggregated as pseudo-CSV, like so:

(1,test,48,,48,,"2016-10-29 00:47:09.348986+00","2016-10-29 00:47:09.348986+00",,1,9,1,1,,1,0,"",,"")
(6,subject,0,,,,"2016-10-30 00:54:38.203023+00","2016-10-30 00:54:38.203023+00",,1,10,1,3,,1,0,content,,)

I'm trying to find the relevant documentation for this behavior. So far I've looked in :

SELECT
Select Lists
Value Expressions
Aggregate Functions

But I can't seem to find any references to it.

For each table that is created, Postgres creates a corresponding composite type:

  Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type.

When you use the table name in the select list, you are essentially using a single column of that composite type with several fields as part of the record (this is a good example on the difference between row and record and column and field).

The "CSV style" just happens to be the default text representation of a composite type.

A row constructor is closely related to this.

This is also the reason why select (a,b,c) from some_table is something different than select a,b,c from some_table in Postgres.


Why am i getting "missing right parenthesis" error when i try to LOG ERRORS when loading from an external table? I've successfully created an error logging table
BEGIN
    DBMS_ERRLOG.create_error_log(
    dml_table_name  => 'enzyme',
    skip_unsupported => TRUE);
END;
/

desc ERR$_ENZYME;

Name            Null? Type
--------------- ----- --------------
ORA_ERR_NUMBER$       NUMBER
ORA_ERR_MESG$         VARCHAR2(2000)
ORA_ERR_ROWID$        UROWID
ORA_ERR_OPTYP$        VARCHAR2(2)
ORA_ERR_TAG$          VARCHAR2(2000)
ENZ_NAME              VARCHAR2(4000)

But i get an error when I try to run this query:
insert /*+ ignore_row_on_dupkey_index ( enzyme ( enz_name ) ) */
into enzyme
SELECT enz_name FROM EXTERNAL ((
  construct_id NUMBER(10),
  n_term VARCHAR2 (50),
  enz_name VARCHAR2 (3),
  c_term VARCHAR2 (50),
  cpp VARCHAR2 (50),
  mutations VARCHAR2 (50),
  mw_kda NUMBER (7, 3))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        skip 1
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    LOG ERRORS INTO ERR$_ENZYME ('INSERT') REJECT LIMIT UNLIMITED) ext
    where not exists (
        select * from enzyme e
        where e.enz_name = ext.enz_name
    );

Error at Command Line : 79 Column : 5
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:
*Action:

Line 79 is the LOG ERRORS INTO line.
If i delete the LOG ERRORS INTO ERR$_ENZYME ('INSERT') part, this command functions perfectly.

Because the DML error logging (LOG ERRORS INTO ...) part belongs to the outer insert, not the inline external table. Move it out from the inner part.
External tables have a different kind of error logging, another file can be specified with the BADFILE clause where the rows with errors are logged.
Update:
Something like this:
insert /*+ ignore_row_on_dupkey_index ( enzyme ( enz_name ) ) */
into enzyme
SELECT enz_name FROM EXTERNAL ((
  construct_id NUMBER(10),
  n_term VARCHAR2 (50),
  enz_name VARCHAR2 (3),
  c_term VARCHAR2 (50),
  cpp VARCHAR2 (50),
  mutations VARCHAR2 (50),
  mw_kda NUMBER (7, 3))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        skip 1
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    ) ext
    where not exists (
        select * from enzyme e
        where e.enz_name = ext.enz_name
    )
LOG ERRORS INTO ERR$_ENZYME ('INSERT') REJECT LIMIT UNLIMITED;


When should I rebuild indexes? When should I rebuild the indexes in my relational database (SQL Server)?

Is there a case for rebuilding indexes on a regular basis?

At the risk of being way too general in my answer, I will say that you should run an index maintenance process regularly.  However, your index maintenance process should only rebuild/reorganize the indexes that specifically require it.

This presents the question:  when does an index require to be rebuilt or reorganized?  Rolando touched on this nicely.  Again, I risk being extremely broad.  An index requires maintenance when the fragmentation level adversely affects performance.  This level of fragmentation could vary based on the size and composition of the index.

Speaking for SQL Server, I tend to choose a index size and index fragmentation level at which point I begin performing index maintenance.  If an index contains less than 100 pages, I will perform no maintenance.

If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics.  If an index is over 30% fragmented, I will REBUILD the index - with no UPDATE STATISTICS, as this is taken care of by the REBUILD. Remember though that a rebuild only updates the statistics object directly associated with the index. Other column statistics will need to be maintained separately.

This answer is really just a long way to say:  Yes, you should do routine index maintenance, but only on the indexes that need it.


invalid object on a table that exists I am getting an "invalid object" warning error in the Management Studio query. Any query against the table works and it links correctly to another table. I am investigating an error that I'm getting in C#. See: https://stackoverflow.com/questions/14409788/is-not-a-foreign-key-column-and-cannot-be-used-here
This is the latest attempt to ferret out what may be going wrong. My linq queries against this table fail, but when I go to classic ADO.NET using SQL strings they work perfectly. Is it possible that this "invalid object" is causing C#/linq to kill the connection?
How do I get the table to stop the "invalid object" error. There are two tables, the following is the simplest one:

USE [Report]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblHoliDate](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Holiday] [date] NOT NULL,
    [Description] [varchar](50) NOT NULL,
    [DOW] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

It is nothing more than a table of holidays used to calculate the business days between two dates.
edit
Any reference to the table will do (which is why I didn't add a query).

SELECT * FROM tblHoliDate

Mouse over the table name to see the error.
The exact error is "invalid object name tblHolidate". This only occurs on mouse-over and the results of any SQL query against the table are complete and correct.

The warning on mouse over should be from the auto complete feature, it's my guess. I've met that a few times. Especially with newly created tables in a session.
Try refreshing the cache for Intellisense in Management Studio and this should fix it.


UPLOCK to avoid multiple reads Given the following table structure...

Orders
======
OrderId int PK

OrderEvent
===========
OrderEventId int PK
OrderId int FK
EventTypeId int FK

I've got multiple concurrent processes SELECT-ing orders but each order should only be read once overall. So upon an order being retrieved I need to add a row in the OrderEvent table to mark that an order has been read (EventTypeId = 1).

So my idea is to have the following query for the read...

SELECT *
FROM Orders o WITH (UPDLOCK)
WHERE NOT EXISTS (SELECT *
                  FROM OrderEvent oe
                  WHERE oe.OrderId = o.OrderID
                    AND EventTypeId = 1)

This runs within a transaction and after the read the order event row is added to mark the order as having been read.

So the question is, will this guarantee that each order can only be retrieved once?

What I'm not sure about is when the lock is put on the order row? Is it before or after the where clause has been evaluated?

The UPDLOCK will prevent a subsequent call from reading an order that has already been picked up, but won't allow that subsequent call from reading others because it will wait to read all the rows that fit the criteria and you have explicitly told the engine that you intend to update them in this transaction.

If you add a READPAST hint as well, it will allow subsequent calls to grab any available rows that have not been picked up previously.

SELECT *
FROM Orders o WITH (UPDLOCK,READPAST)
WHERE NOT EXISTS (SELECT *
              FROM OrderEvent oe
              WHERE oe.OrderId = o.OrderID
                AND EventTypeId = 1)


Balancing the size of oracle datafiles for a tablespace I have a tablespace where I had to add datafiles, because a partion run full. The new datafiles are not filled yet. Can I move data from one datafile to the other?

If a tablespace has multiple datafiles, Oracle manages what goes into what datafile. You have no control over this. If you have enough diskspace to do so, I suggest the following data-juggling-opperation:

Create a new tablespace with a datafile big enough to hold all the data of your source tablespace.
Move all the tables, indexes and other objects to this new tablespace.
Resize the datafiles of your old tablespace how you would like them.
Move all tables, indexes and other objects back to the resized tablespace.
Rebuild all your indexes
Drop the temporary tablespace you created.

Sidenote. If you had used MAXSIZE on your datafiles you would probably not have been in this situation right now... I tend to set MAXSIZE to a conservative size, since I rather run out of datafile size, than out of diskspace...

Some tools to get you going

Create DML statements to move your tables around

select distinct 'ALTER TABLE ' || owner || '.' || segment_name || ' MOVE TABLESPACE target_tablespace;'
from dba_segments
where tablespace_name = 'old_tablespace'
and segment_type = 'TABLE';

Create DML statements to move your indexes around

select distinct 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE target_tablespace;'
from dba_indexes
where tablespace_name = 'old_tablespace'
and index_type != 'LOB';

Resizing datafiles

ALTER DATABASE DATAFILE 'D:\ORADATA\file.DBF' RESIZE xxxMB MAXSIZE xxxMB;

Rebuilding indexes

ALTER INDEX <SCHEMA>.<INDEX> REBUILD;


Loop through slices of 2-dimensional array in plpgsql function? How to loop through an array of arrays in a function and access its elements?

Below is the the function (this is only the relevant part).

create  or replace function public.whatwhatwhy(
        _fields character varying []
    ) returns void as
$func$
declare strSQL text := '';
declare _field text := '';
begin
    foreach _field in array _fields[:][1:2]
        loop
            strSQL :=  'fieldname:' || _field[1] || ' stats: ' || _field[2] ;
            raise notice '% EXECUTING %', to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), strSQL;
        end loop;
end
$func$ language plpgsql;

I want to call the function with:

select public.whatwhatwhy(ARRAY[
['total_pop', 'sum'],
['total_male_pop','sum'],
['median_female_age','median']
])

Question
How do I access e.g. total_pop and sum in my _field? I assumed the _field variable holds another array however I get an error:

  SQL Error [42804]: ERROR: cannot subscript type text because it is not an array
  Where: SQL statement "SELECT 'fieldname:' || _field[1] || ' stats: ' || _field[2]"

Note

I tried this statement, hoping that this solves the problem, but it seems to be identical to the statement above.

select public.whatwhatwhy(ARRAY[
ARRAY['total_pop', 'sum'],
ARRAY['total_male_pop','sum'],
ARRAY['median_female_age','median']
])

A FOR loop over array_upper(_fields,1) like you found is a simple and good option.

Even simpler for your case (for Postgres 9.1 or later): FOREACH with SLICE. The manual:

  With a positive SLICE value, FOREACH iterates through slices of the
  array rather than single elements.

Your function could look like this:

CREATE OR REPLACE FUNCTION public.mj_rubbish(_fields text[])
   RETURNS void AS
$func$
DECLARE
   m text[];
BEGIN
   FOREACH m SLICE 1 IN ARRAY _fields
   LOOP
      RAISE NOTICE '% EXECUTING fieldname: %, stats: %', now()::text, m[1], m[2];
   END LOOP;
END
$func$  LANGUAGE plpgsql;

This also works regardless of actual array indices - which do no have to start with 1 (but do by default).

Related:

Loop over array dimension in plpgsql
Step through multiple arrays in parallel with FOREACH
Normalize array subscripts for 1-dimensional array so they start with 1


Not able to install MSSQL 2014 on an additional disk(some disk other than the OSdisk) silently on Windows 2012 R2 I am providing the following paths during installation(and yes, there's no overriding anywhere)-

InstallSQLDataDir = "R:\Microsoft SQL Server\Instance"
SQLBackupDir = "R:\BACKUP\Microsoft SQL Server\Instance\MSSQL12\MSSQL\Backup"
SQLUserDbDir = "F:\DATA\Microsoft SQL Server\Instance\MSSQL12\MSSQL\SQLData"
SQLUserDbLogDir = "L:\LOGS\Microsoft SQL Server\Instance\MSSQL12\MSSQL\SQLLog"
SQLTempDbDir = "T:\\"
SQLTempDbLogDir = "T:\\"
InstallShareDir = "E:\Program Files\Microsoft SQL Server"
InstallShareWowDir = "E:\Program Files (x86)\Microsoft SQL Server"
InstanceDir = "E:\Program Files\Microsoft SQL Server"

Still, everytime, the MSSQL installation happens onto the OSdisk/System Drive/C drive. Why ?

Still, everytime, the MSSQL installation happens onto the OSdisk/System Drive/C drive. Why

What you are seeing is called binaries of SQL Server and as per rule binaries always goes on system drive and you have no control over it.

If you read SQL Server file location of default and named instance you can get various default locations.


Violation of PRIMARY KEY constraint in SSMS import/export wizard I have table MYTABLE with one PK constraint, generated by a third-party app:
CREATE TABLE [dbo].[mytable](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [FIELD_A] [nvarchar](255) NOT NULL,
  [FIELD_B] [tinyint] NOT NULL,

  CONSTRAINT [mytable_PK] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
  ) ON [PRIMARY],
  UNIQUE NONCLUSTERED ( [FIELD_A] ASC ) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
  ) ON [PRIMARY]
) ON [PRIMARY]

And the data is pretty simple:
ID  FIELD_A     FIELD_B
1   abc         0
2   dfgh        0
3   foo         0

I'm trying to import this data into an empty copy (created with generate scripts wizard/above ddl) of this table in another database using data import, but get the following error:
"Violation of PRIMARY KEY constraint 'mytable_PK'. Cannot insert duplicate key in object 'dbo.mytable'"

I have tried the following (to verify this isn't a duplicate question):

Check that there is no actual duplicate in the destination: The destination is empty
Enabled option "Enable identity insert" (if disabled, this fails with Violation of FOREIGN KEY... instead)
Attempted with both 'Delete existing rows' and 'Append'
Disabled constraint checking: EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
Attempted to not import the surrogate ID - this fails as import wizard sets field to null if ignored instead of leaving empty.
Checked for triggers that may be creating duplicate inserts
Forced SSMS to just drop and recreate the tables

What else can I check to troubleshoot this?

Start by narrowing it down. Does it happen with only this one table? What if you play around with the scripting options (still only doing this one table); uncheck "everything". Etc. Until you have something working and then add bits and pieces back on until you find which one breaks it.
In the end, this is either a problem in the wizard (since duplicates cannot occur in the source data when you have a unique index), or we don't have the full story (hence my recommendation to narrow it down).
If it is a problem in the wizard, then consider reporting it to MS. They will likely want a repro for your problem.


Index not being used in SELECT query I have a table of about 3.25M rows with the follow format in Postgres 9.4.1

CREATE TABLE stats
(
    id serial NOT NULL,
    type character varying(255) NOT NULL,
    "references" jsonb NOT NULL,
    path jsonb,
    data jsonb,
    "createdAt" timestamp with time zone NOT NULL,
    CONSTRAINT stats_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
);

The type is a simple string no longer than 50 characters.

The references column is an object with an list of key values. Basically any list of simple key values, and only ever 1 level deep, the values are always strings. It could be

{
    "fruit": "plum"
    "car": "toyota"
}

or it could be

{
    "project": "2532"
}

The createdAt timestamp is not always generated from the database (but it will by default if a value isn't supplied)

I'm currently using the table with only testing data. In this data every row has a project key as a reference. So there are 3.25M rows with a project key. There are exactly 400,000 distinct values for the project reference. There are only 5 distinct values for the type field, this would probably be no more than a few hundred in production.

So I'm trying to index the table to perform the following query quickly:

SELECT
  EXTRACT(EPOCH FROM (MAX("createdAt") - MIN("createdAt")))
FROM
  stats
WHERE
  stats."references"::jsonb ? 'project' AND
  (
    stats."type" = 'event1' OR
    (
      stats."type" = 'event2' AND
      stats."createdAt" > '2015-11-02T00:00:00+08:00' AND
      stats."createdAt" < '2015-12-03T23:59:59+08:00'
    )
  )
GROUP BY stats."references"::jsonb->> 'project'

The query returns the time distance between two events based on two stats rows that have the same reference. In this case project. There is only ever 1 row for each type and selected reference value, but there may also be no rows in which case the result returned is 0 (this is averaged out later in a different part of a larger query).

I've created an index on the createdAt type and references columns but the query execution plan appears to be doing a full scan instead.

The index

CREATE INDEX "stats_createdAt_references_type_idx"
    ON stats
    USING btree
    ("createdAt", "references", type COLLATE pg_catalog."default");

Execution plan:

 HashAggregate  (cost=111188.31..111188.33 rows=1 width=38)
                (actual time=714.499..714.499 rows=0 loops=1)
   Group Key: ("references" ->> 'project'::text)
      ->  Seq Scan on stats  (cost=0.00..111188.30 rows=1 width=38)
                             (actual time=714.498..714.498 rows=0 loops=1)
          Filter: (
              (("references" ? 'project'::text)
               AND ((type)::text = 'event1'::text)) OR
              (((type)::text = 'event2'::text)
               AND ("createdAt" > '2015-11-02 05:00:00+13'::timestamp with time zone)
               AND ("createdAt" < '2015-12-04 04:59:59+13'::timestamp with time zone)))

Rows Removed by Filter: 3258680
Planning time: 0.163 ms
Execution time: 714.534 ms

I'm really not that clued up on indexing and query execution plans so if someone could point me in the right direction that'd be great.

Edit

As noted by Erwin, it appears as though even if i did have correct indexes, a table scan would still occur as the portion of the table returned from the query is very large. Does this mean that for this set of data this is the fastest query time i can get? Im assuming if i added 60M more unrelated rows without a project reference, it might use an index (if i had the correct indexes), but i dont see how that could speed up the query by adding more data. Maybe im missing something.

According to your current explanation indexes are not going to help much (if at all) with your current query.

  So there are 3.25M rows with a project key.

That's also the total number of rows, so this predicate is true for (almost) every row ... and not selective at all. But there is no useful index for the jsonb column "references". Including it in the btree index on ("createdAt", "references", type) is just pointless.

Even if you had a generally more useful GIN index on "reference" like:

CREATE INDEX stats_references_gix ON stats USING gin ("references");

... Postgres would still have no useful statistics about individual keys inside the jsonb column.

  There are only 5 distinct values for the type

Your query selects all of one type and an unknown fraction of another type. That's an estimated 20 - 40 % of all rows. A sequential scan is most certainly going to be fastest plan. Indexes start to make sense for around 5 % of all rows or less.

To test, you can force a possible index by setting for debugging purposes in your session:

SET enable_seqscan = off;

Reset with:

RESET enable_seqscan;

You'll see slower queries ...

You group by project values:

  GROUP  BY "references"->> 'project'

And:

  There are exactly 400,000 distinct values for the project reference.

That's 8 rows per project on average. Depending on value frequencies we still have to retrieve an estimated 3 - 20 % of all rows if we only picked min and max per project in a LATERAL subquery ...

Try this index, it makes more sense than what you have now:

CREATE INDEX stats_special_idx ON stats (type, ("references" ->> 'project'), "createdAt")
WHERE "references" ? 'project';

Postgres might still fall back to a sequential scan ...

More might be done with a normalized schema / more selective criteria / a smarter query that only picks min and max "createdAt" ...

Query

I would write your query like this:

SELECT EXTRACT(EPOCH FROM (MAX("createdAt") - MIN("createdAt")))
FROM   stats
WHERE  "references" ? 'project'
AND   (type = 'event1' OR
       type = 'event2'
   AND "createdAt" >= '2015-11-02 00:00:00+08:00'  -- I guess you want this
   AND "createdAt" <  '2015-12-04 00:00:00+08:00'
      )
GROUP  BY "references"->> 'project';  -- don't cast

Notes

Don't cast here:

stats."references"::jsonb ? 'project'

The column is jsonb already, you gain nothing. If the predicate was selective, index usage might be prohibited by the cast.
Your predicates on "createdAt" are probably incorrect at lower and upper bound. To include whole days consider my suggested alternative.
references is a reserved word, so you have to always double-quote it. Don't use it as identifier. Similar for double-quoted CaMeL-case names like "createdAt" either. Allowed, but error prone, needless complication.
type

  type character varying(255) NOT NULL,

  The type is a simple string no longer than 50 characters.

  There are only 5 distinct values for the type field, this would probably be no more than a few hundred in production.

None of this seems to make sense.

varchar(255) in itself hardly ever makes any sense. 255 characters is an arbitrary limit without significance in Postgres.
If it's no longer than 50 chars, then the limit of 255 makes even less sense.
In a properly normalized design you would have a small integer column type_id (referencing a small type table) which occupies only 4 bytes per row and makes indexes smaller and faster.

Ideally, you would have a project table, listing all projects and another small integer FK column project_id in stats. Would make any such query faster. And for selective criteria, much faster queries would be possible - even without the suggested normalization. Along these lines:
Optimize GROUP BY query to retrieve latest record per user


SELECT Query is slow when run remotely in MySQL When I connect MySQL from remote server and run below query then the results are very slow(Sometime even faced lost connection Issue). However when I run the same SQL query on the local machine then It's results are very fast.

    SELECT * FROM CUSTOMERS

The customer tale contains two million records.

I have also enable skip-host-cache and skip-name-resolve in MySQL configuration.

Both machine is connected using the LAN IP and customer table is InnoDB table. I am using MySQL 5.6.

Can you please suggest me on this, why SQL query are very slow when run It from remote machine.

Thank you,
Sujeet

LIMIT will work for the first 5000, but will get slower and slower as you move on (with OFFSET) to subsequent chunks.  So that's not a good way to process a large table in chunks.

Does the table have a PRIMARY KEY?  (If not, why not?)  Use that for very efficient chunking.  It will avoid the timeouts you are getting.

A discussion of chunking.  (That is aimed at DELETE, but it can easily be adapted to SELECT.)  The trick is to "remember where you left off".


Send report to email (HTML) I have a procedure with two temporary tables

CREATE TABLE #temp
  ( databasename VARCHAR(100),
    objectname VARCHAR(100),
    index_id INT,
    indexname  VARCHAR(100),
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100)
  )

CREATE TABLE #temp2
(
    databasename VARCHAR(100),
    objectname VARCHAR(100),
    index_id INT,
    indexname VARCHAR(100),
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100)
)

INSERT INTO #temp (databasename,
objectname,index_id,
indexname,
avg_fragmentation_percent,
indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as
 ObjectName,
a.index_id,
b.name as IndexName,
avg_fragmentation_in_percent,
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'

INSERT INTO #temp2 (databasename, objectname,index_id,
indexname,avg_fragmentation_percent,indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as
 ObjectName,
a.index_id,
b.name as IndexName,
avg_fragmentation_in_percent,
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'

and want to send the result from the procedure to email. The problem is that this query works fine but when i execute this its not finding the temporary tables.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'
Report
' +
    N'
' +
    N'
ID	Table	' +
    N'Index	Before fragmentation	After fragmentation	' +
    --N'Expected Revenue
' +
    CAST ( ( SELECT td = T1.databasename,'',
                    td = T1.objectname, '',
                    td = T1.index_id, '',
                    td = T1.indexname, '',
                    td = T1.indextype,'',
                    td = T1.avg_fragmentation_percent AS
                         avg_fragmentation_percent_pre_change, '',
                    td = T2.avg_fragmentation_percent AS
                         avg_fragmentation_percent_post_change
              FROM #temp as T1
              INNER JOIN #temp2 AS T2
              ON T1.databasename = T2.databasename
              AND T1.objectname = T2.objectname
              AND T1.index_id = T2.index_id
              AND T1.indexname = T2.indexname
              AND T1.indextype = T2.indextype
              WHERE T1.databasename not in  ('master',
                                             'model',
                                             'msdb',
                                             'tempdb',
                                             'reportServer',
                                             'ReportServerTempDB')
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'
' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='example@mail.com',
@profile_name='example@mail.com',
    @subject = 'Report',
    @body = @tableHTML,
    @body_format = 'HTML';

Your insert to #temp and #temp2 works perfectly fine.

I believe you are getting the below error.

  Msg 156, Level 15, State 1, Line 70 Incorrect syntax near the keyword
  'AS'.

This error is due to the explicit aliasing on the below part of the code.

td = T1.avg_fragmentation_percent AS avg_fragmentation_percent_pre_change, '',
td = T2.avg_fragmentation_percent AS avg_fragmentation_percent_post_change

since you have already aliased it as 'td'. it will become like this

td = T1.avg_fragmentation_percent , '',
td = T2.avg_fragmentation_percent

Remove the explicit aliasing using AS and run the code and see it works. I did test run
and it worked.


Import big CSV on Oracle I have big CSV files and placed it on Linux (Amazon AMI). I have installed instant client on Amazon Linux AMI and I can connect to my Oracle database using SQL Plus. How can I import all the CSVs to Oracle from Linux terminal?

My Oracle database is located on Amazon RDS.

Use sql*loader to load the data :

http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_params.htm#sthref540

(or external tables).

1) setup a .ctl file with your file format

2) setup / point to a receiving table in your database.

3) invoke sql*loader (sqlld)


TempDB Version Store used by DB STARTUP background process I have two availability group on the same SQL Server 2012 Instance (synchronous commit).
Trace flags 1118 and 1117 are both enabled.
TempDB has been growing for about two weeks: it starts from 36GB and now it's 130GB (4 data files).
I checked if there are any active transactions with DBCC OPENTRAN command but nothing.
I run the following query:
SELECT
    db_name(spu.database_id) as database_name,
    ast.session_id,
    ast.transaction_id,
    ast.transaction_sequence_num,
    ast.elapsed_time_seconds,
    b.program_name,
    b.open_tran,
    b.status,
    ses.row_count,
    (spu.user_objects_alloc_page_count * 8) AS user_objects_kb,
    (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb,
    (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb,
    (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb,
    loginame,
    last_request_start_time,
    last_request_end_time,
    cmd,
    lastwaittype,
    dbid
FROM sys.dm_tran_active_snapshot_database_transactions ast
  --JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id
  JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
  JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
  JOIN sys.sysprocesses b on ast.session_id = b.spid
ORDER BY elapsed_time_seconds DESC

The output shows two rows with:

transaction_id = 0
cmd = "DB STARTUP"
lastwaittype = REDO_THREAD_PENDING_WORK

and one of them has 1202046 elapsed_time_seconds: 13 days of elapsed time roughly corresponds to the beginning of version store problems.
Each row is related to a specific database with DBID 7.
I think, this active snapshot prevent version store cleanup but I can't kill them (transaction_id = 0) and I can't be sure of that: I know version store has 100 GB allocated (I used the following command) but I don't know who use it and who produce the snapshot:
SELECT
SUM (user_object_reserved_page_count)*8/1024.0/1024.0 as user_obj_GB,
SUM (internal_object_reserved_page_count)*8/1024.0/1024.0 as internal_obj_GB,
SUM (version_store_reserved_page_count)*8/1024.0/1024.0  as version_store_GB,
SUM (unallocated_extent_page_count)*8/1024.0/1024.0 as freespace_GB,
SUM (mixed_extent_page_count)*8/1024.0/1024.0 as mixedextent_GB
FROM sys.dm_db_file_space_usage

I checked if in other enviroments the same query on dm_tran_active_snapshot_database_transactions returns the same results and no! It isn't normal having DB STARTUP snapshot active from days.
I checked the errorlog of 13 days ago and I found errors about an unexpected shutdown and failover.
So I tried to remove the database with DBID 7 from the availability group and I re-run the two queries: Now tempdb and version store are empty and the sessions with transaction_id 0 are gone.
I re-added the database to his availability group and I resolved the issue.


Atrocious performance joining INSERTED and DELETED tables in a trigger I've got an UPDATE trigger on a table that watches for a specific column changing from one specific value to any other value. When this happens, it updates some related data in another table via a single UPDATE statement.

The first thing the trigger does is check to see if any updated rows had the value of this column changed from the value in question. It simply joins INSERTED to DELETED and compares the value in that column. If nothing qualifies, it bails out early so the UPDATE statement doesn't run.

IF NOT EXISTS (
    SELECT TOP 1 i.CUSTNMBR
    FROM INSERTED i
        INNER JOIN DELETED d
            ON i.CUSTNMBR = d.CUSTNMBR
    WHERE d.CUSTCLAS = 'Misc'
        AND i.CUSTCLAS != 'Misc'
)
    RETURN

In this case, CUSTNMBR is the primary key of the underlying table. If I do a large update on this table (say, 5000+ rows), this statement takes AGES, even if I haven't touched the CUSTCLAS column. I can watch it stall on this statement for several minutes in Profiler.

The execution plan is bizarre. It shows an Inserted Scan with 3,714 executions, and ~18.5 million output rows. That runs through a filter on the CUSTCLAS column. It joins this (via nested loop) to a Deleted Scan (also filtered on CUSTCLAS), which executes only once and has 5000 output rows.

What idiotic thing am I doing here to cause this? Note that the trigger absolutely must properly handle multi-row updates.

EDIT:

I also tried writing it like this (in case EXISTS was doing something unpleasant), but it's still just as terrible.

DECLARE @CUSTNMBR varchar(31)
SELECT TOP 1 @CUSTNMBR = i.CUSTNMBR
FROM INSERTED i
    INNER JOIN DELETED d
        ON i.CUSTNMBR = d.CUSTNMBR
WHERE d.CUSTCLAS = 'Misc'
    AND i.CUSTCLAS != 'Misc'

IF @CUSTNMBR IS NULL
    RETURN

You could evaluate using explicit INNER MERGE JOIN or INNER HASH JOIN hints but given that you are presumably using these tables again later in the trigger you are probably better off just inserting the contents of inserted and deleted tables into indexed #temp tables and being done with it.

They do not get useful indexes created for them automatically.


If AB->C is an FD for a database then does that imply A->C From below link on Functional Dependencies under FD Axioms(Armstrong's)
Reference Link on research

I came to know that
ab→ c does NOT imply a → c and b → c

But, when I was reading the book "An Introduction to Database Systems" by C.J.Date, on page 279, there is an example that tells about reduction of following set of FDs:

A -> BC
B -> C
A -> B
AB -> C
AC -> D

and under point number 3 of the reduction process it further mentions:
Next, we observe that the FD AB -> C can be eliminated, because again we have A -> C, so AB -> CB by augmentation, so AB -> C by decomposition.

So, that means that if A -> C, then we can imply AB -> C.

Assuming we are given the FD AB -> C in a separate case, then let us assume that A -> C, so AB -> C by above deduction, thus our assumption is true. Then, it proves the data in above link false! Is this mathematical approach not correct or I am wrong in a different way? Please throw some light on each perspective!

I came to know that ab → c does NOT imply a → c and b → c

Correct. It does not imply either.

  ....
  So, that means that if A -> C, then we can imply AB -> C.

Yes, correct. but the following:

  Assuming ... Is this mathematical approach not correct or I am wrong in a different way?

No, A -> C is deducted from A -> BC, not from AB -> C.

A -> BC is just a shorthand notation for A -> B and A -> C


MongoDb replica set: How do I verify data replication on other servers? I have a 3 member replica set running. It's geo-located on 3 separate VPSs. On primary I can do rs.status() and see it's working. I can't execute any mongo shell commands on the secondaries. How do I verify data replication on other servers? I see nothing indicating data being copied in the logs.

Thanks a lot for any help :-)

Edit 1:

Tried db.printSlaveReplicationInfo(). Logged in as user with full admin role...

Edit 2:

For querying secondary run the following command first. After running this, you can do all regular stuff on secondary.

rs.slaveOk()

To see the replication status, run the following command on primary. This will show you if the secondaries are up to date or lagging.

db.printSlaveReplicationInfo()

Hope this helps.


Access denied for user 'me'@'localhost' (using password: NO) I am am trying to learn mysql 101. Doing so, I broke my ability to login as root.

At first I could simply connect to mysql by running

sudo mysql

In an attempt to alleviate a problem, I thoughtlessly copy and pasted some sql into the mysql prompt.

Specifically...

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Now when I try to log in using sudo mysql, I get the error...

  Access denied for user 'me'@'localhost' (using password: NO)

What exactly did I do, and how can I rectify it?

Now you can probably login by

sudo mysql -u root -p

and entering the password from your ALTER USER statement when prompted.

Once you're in the console again you can reset the password to empty like:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

And it should start to allow you with just sudo mysql as before.


SQL Server 2008 database restore I have database Registered and I backup that database from right click on database and backup and it is store registred.bak file.

After I format the system once again I want to restore that database then I follow the step create new database and right click on that newly created database and select restore and I choose file those already I take as back up file (.bak) file and click on restore the database restoring completed after then in right side of explorer view my database name registered (restoring...).

What is restoring point? Because when I click on this database it says

  The dataase not accessible

What is this please explain me ???

Here is a run down to accomplish what you are trying to do. I added screen shots to help you along with backing up and restoring your database. The file paths won't exactly be the same.

BACKUP DATABASE

Backup the database Registered

Click on OK

The database has been backed up

RESTORE DATABASE

To restore the database registered, right click on the Registered Database in SSMS and choose Restore Database

In the window that appears, select From Device and click on the button with three ... to search for your backup

From here, click on Add and locate your backup file

Now click on OK

Click on OK

You are now back in the Restore Database Window, select Options

Make sure to select Overwrite the existing database (WITH REPLACE) and Leave the database ready to use option in the Recovery State options.

Then Click on General in the Select a page zone

Click on the checkbox "Restore" and then click on Script. I prefer sending the restore command to a new windows as a Script instead of directly clicking on OK. This way, I can drop any active connections to the database before restore which could result in a database in use error.

A new query window will be opened with the code necessary to restore the Registered database.

Add the following code just above the restore database script

-- drop all connections and put database in restricted user mode
ALTER DATABASE [Registered] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

-- restore database
RESTORE DATABASE [Registered]
FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Registered.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 1
GO

You will notice that I modified the STATS option from STATS=10 STATS=1. This can be useful when restoring large databases. In the Messages tab, you can view the progression of the restore by %. With STATS=1, the restore can be monitored 1% at a time instead of every 10%

Now hit F5, to run the script.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

13 percent processed.
23 percent processed.
32 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
73 percent processed.
83 percent processed.
92 percent processed.
100 percent processed.
Processed 168 pages for database 'Registered', file 'Registered' on file 1.
Processed 5 pages for database 'Registered', file 'Registered_log' on file 1.
RESTORE DATABASE successfully processed 173 pages in 0.076 seconds (17.783 MB/sec).


FTs catalog in SQL Server for multiple FT index I have created one catalog for FTS index, can the same catalog be used by by another table in index.

Yes, you may add several FTS indexes to a single catalog, but a full text index can only be in a single catalog. Since a full text catalog is a database object, all indexes also have to be in the same database.

If you put all of the full text indexes for a database into a single full text catalog, a catalog reorganization will cover all of the indexes in the catalog.  This is usually fine unless your indexes are quite large.

The SQL Server Books Online describe the CREATE FULLTEXT INDEX here:
http://technet.microsoft.com/en-us/library/ms187317.aspx

If you desire to reorganize one table at a time, then you would want to use a catalog per table.


How long can the log reader agent run for before a restart will do it some good? As a developer who has for the first time successfully implemented a replication topology for our production database, I'm monitoring it very closely and fretting over potential problems.

I'm pleased that the Log Reader Agent has been running for 2 days 20 hours 6 minutes and 2 seconds and don't want to get ahead of myself (as I know that problems will come) but how large will this figure get?

Do dba's ever restart the log reader agent if it's been running for 30, 100 or 365 days? Is this beneficial or is there a website where I can submit a snapshot of Log Reader Agent status if it ever hits 2 years without a problem? (Presuming we have no server reboots - which if course we will).

Ta

I think it depends on your environment and how busy it is but I support quite a few environments that are very stingy with their patches because they have incredibly low surface areas and end up with reboots/SQL restarts pretty infrequently (a few times a year) and the log reader agent isn't restarted outside of this schedule and there are no issues.

If your environment is high transaction and you are experiencing issues with the cleanup jobs on distribution and are running a local distributor you may see a benefit from restarting but, in general, I have found no need to worry about restarting the log reader agent.

I don't believe Microsoft has any particular guidance so this is a YMMV type of question but I think you'll be fine to not restart it outside of your normal patching and reboot/restart policy. As long as you monitor performance and look for issues and symptoms with your replication environment you'll be fine IMHO.


created and deleted a database on mysql slave host by mistake I have 2 hosts: server1 (master) and server2(slave).
By mistake I've created and dropped a database "test" on server2(slave).
If the cluster will switch the master to server2, will there be any issues?

That depends. If the test database on the Master was never in use, then the slave won't care. As long as the slave is not a master to another server, then you are OK.

If the slave has binary logging enabled, the database will just get created and dropped and a record of it would be in the binary logs of the slave.

Besides a test database would not be in use in Production.


Error recover database RMAN-06054 I deleted the test database, and now I want to restore it back, when I try to recover the database I get this error.
Everything I had at the database is there and everything works fine.

How can I fix this error?

unable to find archived log
archived log thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2019 11:49:40
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2 and starting SCN of 422743

SQL> select sequence# from v$archived_log;

 SEQUENCE#
----------
    11
    12
     1
    11
     1
    12

This is normal when performing an incomplete recovery without specifying where recovery should stop.

If sequence 1 is the last log sequence that was backed up, you can avoid the above error by using instead of:

recover database;

this:

recover database until sequence 2;


Delete entry from related tables I can not use InnoDB engine because of some dependency troubles in LuaSQL and PtokaX.

My current query is something like:

DELETE e.*, m.*
FROM entries e
INNER JOIN magnets m
    ON m.eid = e.id
WHERE e.id = %d

where, %d is an integer passed by the user. This query works if there is atleast one entry in the magnets table related to the entries table.

But, it fails when the entries has no dependent row in magnets table. Can I modify this query to perform deletion from both tables, independent of child-rows in magnets? Or do I need to first check if a child exists and delete accordingly?

There are several things you could try:

DELETE Style #1 : LEFT JOIN

DELETE e.*, m.*
FROM `entries` e
LEFT JOIN `magnets` m
    ON m.`eid` = e.`id`
WHERE e.`id` = %d

DELETE Style #2 : Two Independent DELETE queries
(without FOREIGN KEY defined)

DELETE FROM entries WHERE id = %d;
DELETE FROM magnets WHERE eid = %d;

DELETE Style #2b : Two DELETE queries : the order matters
 (when there is a FOREIGN KEY defined between the two tables
 with the ON DELETE NO ACTION property)

DELETE FROM magnets WHERE eid = %d;
DELETE FROM entries WHERE id = %d;

DELETE Style #3 : One DELETE query
 (when there is a FOREIGN KEY defined
 with the ON DELETE CASCADE property)

DELETE FROM entries WHERE id = %d;

Give it a Try !!!


How to setup Email alerts for Reporting jobs when it fails? In SQL server 2008R2, I have a JOB which is running every month 1st week for Reports and I am frequently receiving Error message like "The job failed.  The Job was invoked by Schedule 14. The last step to run was step 1" and Failed step error details: "Message:  Unable to connect to SQL Server '(local)'.  The step failed" Finally I come to know Reports failing because of Database is not fully Restored at the time, when Report schedule starts. After I manually started the Process and Jobs run successfully.

I wan to know when Report jobs failure and when it happen I need to receive some Email alerts (2 types of Error E-mails needed -  one for Errors and another one for Informational etc..), Can I Setup E-mail alerts for Jobs?

SQL server Agent Notification and Setting up an Operator is good Idea? or there is any preferable ways to do that?

You are correct that is the best way for individual server.

Sample code for notification when a job fail.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @jobname=N'test',
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @notify_email_operator_name=N'sysadmin'
GO

You can send email for success/failure or both.

  0 Never

  1 On success

  2 On failure

  3 Always

Books online has details.

There are large shops where instead of setting up notification for each SQL Agent jobs, all failed jobs will be collected at xx minute interval and send one email/raise ticket in the form of a report.


Why do composite foreign keys need a separate unique constraint? Here is a simple table where records may reference parent records in the same table:

CREATE TABLE foo (
    id         SERIAL  PRIMARY KEY,
    parent_id  INT     NULL,
    num        INT     NOT NULL,
    txt        TEXT    NULL,
    FOREIGN KEY (parent_id) REFERENCES foo(id)
);

With the added requirement that one of the other field values (num) must be identical between parent and child records, I thought a composite foreign key should do the trick. I changed the last line to

    FOREIGN KEY (parent_id, num) REFERENCES foo(id, num)

and got ERROR:  there is no unique constraint matching given keys for referenced table "foo".

I can easily add this constraint, but I don't understand why it is necessary, when one of the referenced columns (id) is already guaranteed to be unique? The way I see it, the new constraint would be redundant.

It's a limitation of the DBMS - in all of them as far as I know. And not only when adding a column but also when rearranging columns. If we have a UNIQUE constraint on (a1, a2), we can't add a FOREIGN KEY that REFERENCES (a2, a1) unless there is a unique constraint on that (a2, a1) that essentially is redundant.

It wouldn't be terrifically difficult to add this as a feature:

  When there is a UNIQUE constraint on (a), then any (a, b, c, ..., z) or (b,c, ...a, ...z) combination is also guaranteed UNIQUE.

or the generalization:

  When there is a UNIQUE constraint on (a1, a2, ..., aN), then any (a1, a2, ..., aN, b1, b2, ..., bM) combination or any rearrangement is also guaranteed UNIQUE.

It seems that it hasn't been asked or it hasn't been considered high enough priority to be implemented.

You can always make a request - in the respective channel - for the feature to be implemented. Or even implement it yourself, if the DBMS is open source, like Postgres.


FULL OUTER JOIN With MySQL, MySQL don't support ist what is the solution for more than 2 Tables? select * from

(select col1,col2,col3 from tableA
where col1 like 'somthing1'
and col2 like 'somthing2' ) as t1

left join

(select col2,col2, col3 from tableB
where col2 like 'somthing1'
and col2 like 'somthing2') as t2

on t1.col3= t2.col3

left join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t3

on t2.col3= t3.col3

left join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t4

on t3.col3= t4.col3

UNION

(select col1,col2,col3 from tableA
where col1 like 'somthing1'
and col2 like 'somthing2' ) as t1

right join

(select col2,col2, col3 from tableB
where col2 like 'somthing1'
and col2 like 'somthing2') as t2

on t1.col3= t2.col3

right join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t3

on t2.col3= t3.col3

right join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t4

on t3.col3= t4.col3

Build a VIEW that simulates a FULL OUTER JOIN of A and B.  (Use the template given by some of the links.)
Build a VIEW that simulates a FULL OUTER JOIN of that VIEW and C.  (Use the template given by some of the links.)
Etc.

Eventually, you will have a FULL OUTER JOIN of all the tables without the ugly mess of compounding the number of joins needed.


Check of SQL Server upgrade success I have performed SQL Server upgrade from 2005 to SQL Server 2008. Is there any tools/possibilities to check, whether the upgrade from SQL Server 2005 to SQL Server 2008 has been accomplished successfully?

There are no tools, so to speak. At least not that I am aware. The best thing to do is to test your code, verify your configurations and ensure that SQL Server is functioning optimally. I am hoping that you did this upgrade in a dev or test environment first and that you followed a process with steps that you can repeat again in production. If that's the case, do a full test of your code that interacts with SQL Server. Ensure everything functions as it did before and make sure that all of your maintenance works and that everyone can connect to it at as before.

I would also look in your SQL Server Error Logs and see if there are any issues. Same thing with your windows event logs (particularly your system and application event log)

I recently gave an answer here that talks a whole lot more about SQL Server Upgrades. Lastly, have you considered what you'll do about DB Compatibility Level? If you haven't, you might want to think about changing that for your databases to SQL 2008 and seeing if all works after a regression test. But please make sure you are in a dev or test. I do not advise just changing this in production, nor do I advise starting with an upgrade in production.


Very high (99%) insertion cost in estimated execution plan I have four tables with 500,000 to 4,000,000 rows. The script joins them and insert into a temporary table with some where clause. It is taking long. The execution plan shows 99% cost only for the insert whereas there are multiple scans in the plan. Snapshot of the related part of the execution plan is shown in the image. The drive is solid state. How do I interpret the plan. SQL Server execution plan

The high relative cost for the insert is a result of the cost-based model used by the optimizer.  In particular, it looks like SQL Server assigns a much higher I/O cost to inserts into a temp table compared to reading rows from a heap or clustered index.

The percentages you see are purely based on these cost estimates.

As a simplified example, check out this execution plan, which uses the Stack Overflow database:

SELECT *
INTO #tempusers
FROM dbo.Users;

I'm inserting all of the rows from the dbo.Users table (about 300,000) into a temp table.

The differences in estimated cost between the index scan and the table insert operators is dominated by "Estimated I/O Cost:"

Clustered Index Scan

Estimated rows: 299,611
Estimated I/O cost: 5.42312

Table Insert

Estimated rows: 299,611
Estimated I/O cost: 1,104.13

The estimated I/O cost for the temp table insert is two hundred times higher than the estimated I/O cost of the index scan.

Looking at the actual execution plan, you can see that the scan took 386 ms, and the insert took 514 ms.  The insert, in practice, was definitely not 200 times more "expensive" by any measure.

All of this is to say that, in this case in particular, don't focus on those percentages.  The I/O cost estimates are a bit outdated, and were originally based on systems with spinning disk storage and smaller amounts of RAM.

If you want help with specific parts of the query in your screenshot, you should post a new question that includes the actual execution plan.  I see a few sorts in the screenshot, which can often be avoided with indexes.  You also mentioned that what is inserted into the temp table is filtered - indexes can help with that as well.


Find all values not present for other values of other column Let's suppose I have a table with integer columns a and b (each pair (a,b) is unique) and following data:

 a | b
-------
 1 | 2
 1 | 3
 1 | 4
 1 | 5
 2 | 1
 2 | 3
 2 | 4
 3 | 1
 3 | 3

Now I need to get all the rows whose b value is not contained in the set with some other a value (i.e. they appear just once in the union of rows with the 'a' values in question). From these I should get (1,2), (1,5) and (2,1).

I want to include only two sets (groups with the same a value; I want to use this as part of a function, where both a values are given as parameters) at a time, so I don't want to get (3,1) from the new data.

I have some (working) solution; I post it as an answer. However, there should be something more elegant - the query should be one and exploit the symmetricity, not two unioned queries. I thought of various solutions listed in this answer, but after some hour of playing with code I didn't find how to make anything better and working.

If your pairs are unique, as in the example you gave, you want the values of b that appear only once, and this would be a solution:

SELECT a,b
FROM tbl1
WHERE b in (
  SELECT b
  FROM tbl1
  GROUP BY b
  HAVING (count(b)) = 1
  )

You can see it in this fiddle http://sqlfiddle.com/#!15/c82d3/3


MySQL Dying After a Few Days I just recently upgraded to Ubuntu 15.04 from 14.04, I guess the MySQL was also upgraded to 5.6. Is there a reason why my database stops working. Rebooting the system is the only fix for it, but it still dies again in a day or two. It's been going on for a few weeks since I updated to 15.04. I also removed and reinstalled MySQL.

2015-09-22 22:16:28 14273 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future$
2015-09-22 22:16:28 14273 [Note] Plugin 'FEDERATED' is disabled.
2015-09-22 22:16:28 14273 [ERROR] Function 'innodb' already exists
2015-09-22 22:16:28 14273 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-09-22 22:16:28 14273 [ERROR] Function 'federated' already exists
2015-09-22 22:16:28 14273 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-09-22 22:16:28 14273 [ERROR] Function 'blackhole' already exists
2015-09-22 22:16:28 14273 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-09-22 22:16:28 14273 [ERROR] Function 'archive' already exists
2015-09-22 22:16:28 14273 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-09-22 22:16:28 14273 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-09-22 22:16:28 14273 [Note] InnoDB: The InnoDB memory heap is disabled
2015-09-22 22:16:28 14273 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-09-22 22:16:28 14273 [Note] InnoDB: Memory barrier is not used
2015-09-22 22:16:28 14273 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-09-22 22:16:28 14273 [Note] InnoDB: Using Linux native AIO
2015-09-22 22:16:28 14273 [Note] InnoDB: Using CPU crc32 instructions
2015-09-22 22:16:28 14273 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2015-09-22 22:16:28 14273 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2015-09-22 22:16:28 14273 [ERROR] Plugin 'InnoDB' init function returned error.
2015-09-22 22:16:28 14273 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-09-22 22:16:28 14273 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-09-22 22:16:28 14273 [ERROR] Aborting

[Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12

errno 12 is Out of memory - So probably you did not set any value for the innodb_buffer_pool_size and the default is used. Depending on your previous version the default might change on upgrade. Or you did have it configured but the upgrade rewrote your config? Or your new Ubuntu just needs more memory for itself.

Change the value, add more RAM, find some memory hog which might eat your RAM or enable swap (might slow everything down when it hits the swap).


Is there a difference between making a composite foreign key for all columns or a foreign key for each column? In general, is there a difference between making a composite foreign key for all columns or a foreign key for each column?

First approach

FOREIGN KEY(OrderId, CompanyId) REFERENCES ParentTable(OrderId, CompanyId)

Second approach

FOREIGN KEY(OrderId) REFERENCES ParentTable(OrderId)
FOREIGN KEY(CompanyId) REFERENCES ParentTable(CompanyId)

Sure, there is a big difference. In first case you are referencing by combination of columns. It means ParentTable should contain a row for each combination of OrderId and CompanyId which you want to add to your ChildTable. In the second case it would be enough to have separate rows with corresponding OrderId and corresponding CompanyId.


Get the rank of a row based on a score I am generating from other field values I am trying to figure out how I can get the rank of a row from a score I am generating from other field values.

The score query looks something like this

SELECT ((table2.field1*0.4) + (table2.field2 * 0.2) + (table1.field1*0.7)) 'score' FROM `table1` LEFT JOIN table2 ON table2.table1_id=table1.id GROUP BY table1.table1_id ORDER BY `score` DESC

This generates my scores, they look something like this

|4363.195
|3970.4210000000003
|2781.8
|2601.2980000000002
|2508.905
|2269.829
|2204.1
|1893.367
|1789.575

I am trying to get rank of each provider in a query like this

SELECT rank_here, table2.* FROM `table2` WHERE 1

This is just sample stuff I am just trying to figure out how to do this(this is why I have also included the score being generated from 2 tables, but it really doesn't matter it can be from a single table or even more than 2)

I researched :
Get the rank of a user in a score table

But all I found is how to get the rank if I have the score in a field, and in my case I need to make it based on other field values.

You want 1, 2, 3, ... tacked onto the output?

SELECT @rank := @rank + 1,
       s.*
    FROM ( SELECT @rank := 0 ) AS init
    JOIN (
        SELECT ... -- your existing SELECT
         ) s;


Your appear to have a couple of problems
1) you are referencing the T2.INI column in the UPDATE statement, but T2 only exists within the EXISTS statements.
2) The CONVERT statements on the date are messing up the UPDATE.

While the following may not be perfectly identical in functionality, it does actually run

;WITH CTE AS(
--GROUP BY IDHR
SELECT row_number() OVER (PARTITION BY T.IDHR ORDER BY T.ULT_ACT ASC) AS IDOR,
T.VALIDO,
T.ID,
T.INI AS INI,
T.FIN AS FIN,
T.ULT_ACT AS ULT_ACT,
T2.fin as T2FIN
FROM tstOver T
inner join tstOver T2
    on T.ID <> T2.ID
    and (T2.INI BETWEEN T.INI AND T.FIN) OR
    T2.INI = T.FIN
    --WHERE EXISTS
    --    (
    --        --CRITERIA FOR OVERLAPS
    --        SELECT 1 FROM tstOver T2
    --        WHERE T.ID <> T2.ID AND
    --            (T2.INI BETWEEN T.INI AND T.FIN) OR
    --            T2.INI = T.FIN
    --    )  )
--SETTING VALIDO TO 0 WHEN THEY OVERLAP AND MAKING RECORDS SEQUENTIAL DATE-WISE
UPDATE CTE SET VALIDO = 0, FIN = (CTE.T2FIN - 1) --4406 BECAUSE T2.INI IT'S DERIVED FROM THE SUBQUERY
  where IDOR > 1


Correct database solution? So I work for a smaller company and currently we need to do our data processing with a desktop solution. We're running into memory limitations and the overall solution in most people's eyes (non-technical staff) is that setting up a database for the data and doing our work on a database instead of the massive flat files (SPS files mostly or the lesser known Triple-S) we currently work on.

I would likely be the one that would be working within this database to do all the data cleaning, validation and variable creation and so I'd like to make sure what I imagine is possible and to also see other possible options.

The data we are using is respondent level survey data. We will be updating the database every quarter with about 40,000 respondents and we have about 40,000 vectors of data per respondent (largely sparse data).

One of the more advanced calculations and operations I would need to do is, I would need to determine the standard deviation of a vector of data and then reduce a respondent's response to no more then 2 standard deviations away from the mean.

Also, creating new vectors of data. (When grouping respondents we will often mark them as apart of 1 of many groups and I would need a vector containing this data to work off of and this would most likely become its own table with various other data.)

I guess I'm just looking for some guidance, I have no problem learning SQL or using MySQL or whatever but I don't know if those are the correct solutions or if I should be looking at something else all together. I know what I need to do to the data but I don't know the best platform to do it on.

I apologize if this is too broad a subject but any guidance is appreciated.

My initial reaction is that you should have everything you need in a standard Microsoft SQL Server installation. Integration Services to do the ETL from your flat files into SQL DB engine. Then the T-SQL language and for your calculations and operations, .NET for more advanced operations. Analysis Services and Report Server engines for the online analysis/drilldowns and for producing reports for clients and staff. Couple that with MS Office, Excel and PowerPivot, and you'll have a complete suite of Business Intelligence tools for all your needs and more.

Just my 2c

Good luck!


Can I restore my SQL Server database to a point before the last full backup with my current backup strategy? If I have this backup strategy(weekly full backup and 1 hour log backup), can I restore the database to the green highlighted period of time? Btw, does log backup 2 includes all log records that have their lsn great than the last_lsn of log backup 1?

Update
I did some more test and I think I found the answer. According to the image in the backup schedule, I did an initial full backup, some log backups, then log backup 1, a full backup, log backup 2. I have modifications in between. Use RESTORE HEADERONLY to check log backup 1, full backup and log backup 2, below is what I got. As you can see, log backup 2 catches all log records from the last one in log backup 1. If I want to restore to a point in the green highlighted period, I need to use log backup 2, not the full back.

Yes, you could do a point-in-time restore.
You would need the last full backup and all log backups up to the point you need, as log backups do not contain all earlier logs (they are in one sense incremental, rather than differential).
For a week's worth of logs on a busy database, it may take some time to restore. Therefore a better solution (if you want to stick to weekly fulls) is to do a differential backup every day or twice a day.
This means that you only need the last full backup, the last diff backup, and all logs going forward up to whenever you want to restore.

A fairly standard backup cycle on many servers is: full daily, diff 4-6 per day and logs every few minutes. This gives a pretty fast restore time. It also means more importantly, that your maximum data loss is never more than a few minutes, rather than up to an hour.


How to Manage CPU Usage in Oracle Database 11g using Instance Caging? I am doing a little test of the cpu utilization case, got to know that i can use Instance Caging for the same to improve my resource utilization. Can somebody let me know how to do this? i m using oracle 11g.

Instance caging is a feature in the Enterprise Edition of Oracle Database 11g Release 2 (11.2) that simplifies the management of CPU usage in consolidation environments. By enabling Resource Manager and setting the CPU_COUNT parameter in each instance, you can limit the maximum amount of CPUs/Cores the instance can use. You have to notice three points for this.

1. Enabling Resource Manager
2. Setting CPU_COUNT
3. Monitoring Instance Caging

1. Enabling Resource Manager
Resource Manager has been available in the Oracle database.
Resource Manager is not enabled by default, so it must be enabled by specifying a resource plan before instance caging can take effect.

 SELECT plan FROM dba_rsrc_plans;

  PLAN
 ------------------------------
 DEFAULT_PLAN
 INTERNAL_QUIESCE
 INTERNAL_PLAN
 APPQOS_PLAN
 DEFAULT_MAINTENANCE_PLAN
 ETL_CRITICAL_PLAN
 MIXED_WORKLOAD_PLAN
 ORA$AUTOTASK_SUB_PLAN
 ORA$AUTOTASK_HIGH_SUB_PLAN
 DSS_PLAN

 10 rows selected.
 SQL>

If you have no specific resource management needs within the instance, the simplest solution is to use the default plan.

   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;

2. Setting CPU_COUNT

ALTER SYSTEM SET cpu_count = 2;

3. Monitoring Instance Caging

The throttling effect of Resource Manager can be displayed using the CONSUMED_CPU_TIME and CPU_WAIT_TIME columns of the following views.
The CONSUMED_CPU_TIME is the number of milliseconds of CPU time consumed by the consumer group, while the CPU_WAIT_TIME is the time waiting for CPU due to Resource Manager throttling.

SELECT name, consumed_cpu_time, cpu_wait_time
FROM v$rsrc_consumer_group;

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SET LINESIZE 100
SELECT begin_time,
   consumer_group_name,
   cpu_consumed_time,
   cpu_wait_time
FROM   v$rsrcmgrmetric_history
ORDER BY begin_time;


A query for results based on date range I have a table called invoice and I have a query that will give me the sum of all invoice amounts for a given account where the invoice has a status of PAID between a certain date range.

Below is the schema and query.

Schema:

CREATE TABLE invoice (
    id             INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    agreement      BOOLEAN NOT NULL DEFAULT 0,
    invoice_number VARCHAR(255) NOT NULL,
    status         VARCHAR(255) NOT NULL INDEX,
    status_text    VARCHAR(255) NOT NULL,
    currency       VARCHAR(255) NOT NULL,
    amount         INT(10) UNSIGNED NOT NULL,
    description    VARCHAR(500) NOT NULL,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP INDEX,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id),
    UNIQUE (invoice_number)
);

CREATE TABLE account_invoice (
    account_id INT(10) UNSIGNED NOT NULL,
    invoice_id INT(10) UNSIGNED NOT NULL,
    --
    INDEX (invoice_id, account_id),
    PRIMARY KEY (account_id, invoice_id),
    FOREIGN KEY (account_id) REFERENCES account (id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoice (id) ON DELETE CASCADE
);

Query:

SELECT SUM(invoice.amount)
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ?
    AND invoice.updated_at <= ?
    AND invoice.status = "PAID"

Let's assume our date range starts on Jan 01 2018, ends on Jan 02 2018.

Let's also assume I have five invoices:

updated_at is Jan 01 2018, amount is 1000
updated_at is Jan 01 2018, amount is 2000
updated_at is Jan 02 2018, amount is 50
updated_at is Jan 03 2018, amount is 100
updated_at is Jan 04 2018, amount is 100

Given the data above, the query above will give me a result of 3050. Perfect.

Now what I want to do is take the date range, and get a list, as opposed to the SUM(amount) of matching invoices. For each invoice matched against the query, I want to take its date and amount.

As such, the result set should look like this:

[
   {value: 1000, date: "Jan 01 2018"},
   {value: 2000, date: "Jan 01 2018"},
   {value: 50,   date: "Jan 02 2018"}
]

Using that result set, I want to be able to make a second condition to shrink down the result set further based on on an interval of time, e.g. daily, weekly, monthly, yearly.

So if I use a daily interval from the result set above, I should get this:

[
   {value: 3000, date: "Jan 01 2018"},
   {value: 50,   date: "Jan 02 2018"}
]

If I use a weekly interval, I should get this:

[
   {value: 3050, date: "Jan 01 2018"},
]

...and so on. As you see it just essentially does the sum logic of my first query on the result set of my second query.

My current attempt at the first part (no interval logic) is the following query:

SELECT invoice.amount AS value, invoice.updated_at AS date
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ?
    AND invoice.updated_at <= ?
    AND invoice.status = "PAID"
GROUP BY invoice.id

This does not give me the result I'm looking for, so I can't hope to move to the second part with intervals yet. Any guidance would be appreciated.

UPDATE:

SELECT invoice.amount, %s AS "invoice.updated_at"
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ?
    AND invoice.updated_at < ?
    AND invoice.status = "PAID"
GROUP BY invoice.id, invoice.amount, FLOOR(TO_DAYS(invoice.updated_at) - 2) / 7

Your first resultset comes from

... WHERE updated_at >= ...
      AND updated_at <  ...

Your second resultset comes from adding this on

... GROUP BY DATE(updated_at)

The third ("weekly") gets trickier:

... GROUP BY FLOOR(TO_DAYS(updated_at) - 2) / 7

(Where the "-2" is adjusted to make a "week" start on Sunday or Monday or whatever you prefer.)

Yearly:

... GROUP BY YEAR(updated_at)

Monthly:

... GROUP BY LEFT(updated_at, 7)

(because the LEFT will give you something like the string "2011-03").