texts: PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

texts:
I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases:

/usr/lib/postgresql/9.3/bin/pg_dump --schema-only
                                    -h stg-db.....rds.amazonaws.com
                                    -U user_name db_name > stg_db_schema.sql

The command works well on my dev db, but fails on stg and prod due to:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation direction_lookup
pg_dump: [archiver (db)] query was: LOCK TABLE tiger.direction_lookup IN ACCESS SHARE MODE

The problem seems to be permissions for tiger.direction_lookup. I Googled a bit, and this table seems to be related to PostGIS.

I have tried --exclude-table-data=tiger.direction_lookup, with no effect (it is not supposed to work for schemas anyway).

Any idea how to be able to dump the schema of my DB?


texts:
how can i take last used index from a specific table and add a +1 to it?

database login, table cars

I think i need to add it here ####

  $sql = 'INSERT INTO '.$this->options['db_table']
                  .' (carID,name, size, type, title, description)'
                  .' VALUES (#############,?, ?, ?, ?, ?)';


texts: Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server X

texts:
I am getting this error while doing concurrent read from same table through linked server. Time out setting is alright. When this error happens, I also start getting the following error for the same query:

"Transaction (Process ID X) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.."

Does this mean that there is a limitation to the number of reads that can be performed through linked server ? If so, what is the limit ? or could this be happening because of some other reason ?

Also the query used is as follows :

SELECT  T.Col1, T.Col2, T.Col3, PG.Col1, PG.Col2, PG.Col3
INTO #TMP_OUTPUT
FROM  linkedserver1.db1.dbo.table1 T WITH (NOLOCK)
      INNER JOIN #temp2 PG
      ON T.Col1 = PG.Col1
WHERE T.Col3 = somevalue

Thanks,
Kanu


texts: how to Export a document contain tables name, columns names and column description from SQL Server 2008?

texts: I created a db in sql server 2008 and I wrote for some columns description to describe them, What I want now is to find a way to export these tables design including the description for each column into a document to be able to discuss with the teams and be printed.


texts: Can not install oracle apex on GNU/Linux (db 12c)

texts:
I have to install oracle apex on oracle database 12.2.0.1.0, database is on GNU/Linux server.
I followed instructions from this pages:

https://oracle-base.com/articles/misc/oracle-application-express-apex-5-0-installation#installation]

https://docs.oracle.com/cd/E71588_01/HTMIG/downloading-and-installing-OracleAE.htm#HTMIG29384]

but when i came to this step:

@apexins.sql APEX APEX TEMP /i/

I got this error:

  PL/SQL procedure successfully completed.

  PL/SQL procedure successfully completed.

  PL/SQL procedure successfully completed.

  Performing installation in multitenant container database in the
  background. The installation progress is spooled into apexins_cdb*.log
  files.

  Please wait...

  catcon: ALL catcon-related output will be written to
  [/u01/app/oracle/product/12.2.0/dbhome_1/apex/apexins_cdb_catcon_64683.lst]
  catcon: See
  [/u01/app/oracle/product/12.2.0/dbhome_1/apex/apexins_cdb*.log] files
  for output generated by scripts catcon: See
  [/u01/app/oracle/product/12.2.0/dbhome_1/apex/apexins_cdb_*.lst] files
  for spool files, if any validate_con_names: ORCLPDB is not open
  catconInit: Unexpected error returned by validate_con_names Unexpected
  error encountered in catconInit; exiting

  Installation completed. Log files for each container can be found in:

  apexins_cdb*.log

  You can quickly scan for ORA errors or compilation errors by using a
  utility like grep:

  grep ORA- *.log grep PLS- *.log

So i find solution for that error on this link:

https://learn-oracle-database.blogspot.com/2016/04/validateconnames-pdbseed-is-not-open.html

I have done, everything like is described on the link, and database was in this state:

SQL> select name,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
MIGRATE

ORCLPDB
MIGRATE

And I came to final step, and executed this order:

$ORACLE_HOME/perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin /tmp/utl32k_output utl32k.sql

Then I got this error message:

  /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/tmp/utl32k_output_catcon_
  61307.lst as CATCONOUT catconInit: Unexpected error returned by set_log_file_base_path print() on closed filehandle $CATCONOUT at
  catcon.pm line 6702. Unexpected error encountered in catconInit;
  exiting

I tried to find solution but i could not, so if someone could help what I have to do, to solve this problem.


texts: Displaying a value associated with a maximum value

texts:
I have a PostgreSQL database for a supermarket (it's a toy problem) and I need to find which outlet has sold the most copies of each product and display it in a query which also shows the product name, description, copies in stock across all stores, copies purchased across all stores.

I think I have the first few columns queried appropriately, as below:

SELECT a.ProductName, a.ProductDescription, a.StockSum, b.PurchaseSum, c.MaxSales
FROM (SELECT Product.Name AS ProductName, Product.Description AS ProductDescription
           , SUM(Stock.copies) AS StockSum
FROM Product
INNER JOIN Stock
ON Stock.product_id = Product.product_id
GROUP BY Product.name, Product.description) AS a
FULL JOIN
(SELECT Product.name AS ProductName, Product.description AS ProductDescription
      , SUM(PurchaseItem.copies) AS PurchaseSum
FROM Product
INNER JOIN PurchaseItem
ON PurchaseItem.product_id = Product.product_id
GROUP BY Product.name, Product.description) AS b;

But I cannot for the life of me work out how to pull the appropriate Outlet.name associated with the MAX of the SUM of all PurchaseItem.copies corresponding to a particular product_id at a particular store. It seems like a very complex query, and it has flummoxed me!

The structure of the database is that Purchase references Outlet, while PurchaseItem references Purchase and a single Product (with PurchaseItem.copies recording the number of sales of that product in the purchase).


texts: Exporting schema data (tables, permisions) into another schema on same database

texts: I want to take data from one schema schema1 and put it into schema2 this should be quite easy to do but i have searched through the oracle documentation and can't find a way to do it.


texts: SQL Logins in db_owner role on database cannot create stored procedures

texts:
I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the account the db_owner role in the database. The developer reports that he can no longer create stored procedures.

I have validated this is true in this particular SQL 2014 Instance, I cannot reproduce it anywhere else. I have done about everything I can think of to fix this. The best clue I have is that I created a new SQL login, and gave it db_owner in the same database. It also cannot create a procedure. The original login cannot create procedures in other user databases even though it has db_owner.

Attempting to create a procedure gives:

  Msg 262, Level 14, State 18, Procedure TheProcedure, Line 30
  CREATE PROCEDURE permission denied in database 'DBTest'.

The error message is correct, CREATE PROCEDURE does not show up for the user, even after I explicitly assign it. The user can Alter any procedure. UDF creation gets a similar error:

  CREATE FUNCTION permission denied in database...

Anyone have an idea of what could be happening?

When I do a:

select * from fn_my_permissions(null, 'DATABASE')

CREATE PROCEDURE is not listed. I have checked for any DENY in sys.permissions, there is none.

The following query:

SELECT * FROM fn_my_permissions('xyz', 'USER');

returns:

IMPERSONATE, VIEW DEFINITION, ALTER, CONTROL


texts: DevExpress formats columns differently if they're all numbers

texts:
I have a table with a 'Code' column, defined as varchar.  However, if you have only numbers in some of the records, for this field, and you search on these records in our application, the results in grid (DevExpress) is formatted as numbers.

The moment you have records in the result that contains a-z, then the formatting does not happen.  So I'm trying to see if i can fix this from the database side.  I have created a view on the table, and changed the column in question to:

:
Code + CHAR(144) AS Code
:

This solves the formatting of the field in the grid.  The field will now always been seen as a string, even if the results only contains numbers.

The problem is however, if I now explicitly search on this value in our application's front-end, eg Code = 40E, I get no results, due to the special character (which is not visible).  So I have only partly solved the issue.  I have tried to add just a trailing space to the field at first, but that did not work.

Any ideas how this can be solved from the database, if possible at all?

I only have access to the SQL objects, hence I was trying to fix this through SQL.  Is there any way to address this in a database view, but also ensuring that there are no funny characters appended to the field value which affects the searching?


texts: Get the matching condition in the SELECT list

texts:
Let's say my full text search query is below:

USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO

I need one more column that shows which condition was matched in the CONTAINS clause.

SELECT Name ,Matched_condition
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');

+---------------------+-------------------+
| name                | matched_condition |
+---------------------+-------------------+
| chain smokers       | chain             |
+---------------------+-------------------+
| full throttle       | full              |
+---------------------+-------------------+
| chain full of smoke | chain,full        |
+---------------------+-------------------+

What should I use to get the new column (matched_condition)?

I have a list of conditions that includes 200 words.


texts: Tablespace error SQLCODE=-289, SQLSTATE=57011

texts:
We are trying to bulk insert ~200 million records as initial load into a DB2 database.

During the process we get the following error:

  SQLCODE=-289, SQLSTATE=57011, SQLERRMC=TEST_IS

Does it mean we ran out of disk space?
How to add additional space?

Please find the following details of the tablespace:

it is a Database-managed space.
type is large table space,
EXTENTSIZE is 32
PAGESIZE is 32768

I am not a DBA and not able to understand the error.


texts: Connecting to a Synology Media Server database

texts:
Synology NAS's provide a multimedia server backed by a PostgreSQL database. You can connect to that database from an SSH session:
sudo su -
su - postgres
psql mediaserver postgres

Now, I'd love to be able to connect to the database from any computer on my home network, e.g. using pgAdmin, rather than through an SSH terminal. My NAS is not exposed to the Internet and is behind my router's firewall, so I only need to open this to computers on my home network.
Can anyone guide me on how to get there? I have no knowledge whatsoever on PostgreSQL, as I'm more experienced with MySQL.


texts: (MySQL) Need an "in-place" REPLACE for adjusting historical data

texts:
I have a table with around 6,000,000 records.  It is backed up every few hours.  Recently a script ran that cascade updated about 2,000,000 of the older records to bad values.  I have extracted those 2,000,000 records from the backup and have them ready for re-insert, in the form of  "INSERT INTO table (col, col) values (val, val)"

Now, I need a mechanism to insert those 2,000,000 records, replacing the bad data with the historical data, while maintaining existing IDs (meaning REPLACE is not an option -- as it deletes on duplicate and increments the ID).  Also, I need to do this without interrupting server access for too long (which means DROP TABLE, then --force trickery is not an option).

I'm sure I'm missing something, but I can't figure out for the life of me how to replace in-place within the current parameters.  Thoughts?


texts:
I have mysql 5.7.24 mysql version. Can i implement Multi-master replication between three nodes without to create a ring topology? Like a graph

Thanks :)


texts: Count instances of value from one column in another column located in another table

texts:
Let's say I have the following tables :

Table 1

task | date
100  | 2016-01-01
101  | 2016-01-01
103  | 2016-01-01

Table 2

task2 | date
113   | 2016-01-01
113   | 2016-01-01
103   | 2016-01-01

I would like to get to this result:

task_result | Count
100         | 0
101         | 0
103         | 1

Basically count data included in task1 table1 present in task2 table2.

Any idea?


texts: SQL 2008 R2 3rd Party Peer-to-Peer Replication, Global Site Distribution

texts:
We are looking at hosting 3 globally distributed SQL Server installations at different data centers. The intent is that Site A will serve web traffic and data for a specific region, same with Site B and C. In the case that Site A data center goes down, looses connectivity, etc. the users of Site A users will fail over to Site B or C (depending which is up). Also, if a user from Site A travels to Site C they should be able to access their data as it was on Site A.

My questions is what SQL replication technology (SQL Replication or 3rd party) can support this scenario? We are using SQL 2008 R2 Enterprise at each site, each site runs on top of VMWare with a Netapp filer. Would something like distributed caching help in this scenario as well?

We have looked at and tested Peer-to-Peer replication but have encountered issues with conflicts during our testing. I imagine there are other global data centers that have encountered and solved this issue.


texts: Azure SQL | Granting Security Groups DB Role permissions

texts:
I am trying to grant a Security Group within our company's Azure environment that is found in Azure AD. I have successfully granted users' rights to the db_datareader, writer, owner roles. But I am trying to add a Group to one of these roles and the error I get below.
I am an AD Admin of the SQL Server in Azure, and I confirmed that the Security Group is synced and found in our Azure AD environment. I have also confirmed running the commands below against the actual database and the master db.
I know that within Azure SQL you can run the T-SQL command to add a user by their UPN, this works fine:
CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datawriter] ADD MEMBER [bob@contoso.com];

But when running the below it provides an error
ALTER ROLE [db_datawriter] ADD MEMBER [groupname@contoso.com];

Msg 15151, Level 16, State 1, Line 1
Cannot add the principal 'groupname@contoso.com', because it does not exist or you do not have permission.

Do I also need to create a Group for the Sec Group as well? As in:
CREATE GROUP [groupname@contoso.com] FROM EXTERNAL PROVIDER; -- "GROUP" is not correct syntax.

or
CREATE USER [groupname@contoso.com] FROM EXTERNAL PROVIDER;

This article did not provide any details I could find on this topic but adding users.


texts: How does JOIN involving database across 2 servers store the intermediate data?

texts:
In case of a SQL JOIN on 2 databases across servers.
For example:
Select *
From server1.db1.dbo.tbl1 a
Join server2.db2.dbo.tbl2 b
On a.common_id=b.common_id
Where a.some_col = 1000

Suppose this query is run on server 2 via SSMS, then where does the query processing work? For example:

Data for server1.db1.dbo.tbl1 needs fetching

Then data for server2.db2.dbo.tbl2 needs fetching

Then join to be performed.

Then a filter

Where (on which server) does each of the above operation happen?


texts: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

texts:
I received the error that is in question, but do not have a north where to start investigating the cause of this error, ie not a North where to start. I'm getting worried about the situation. can anybody help me?

My execution stack is like this:

TARGETSITE: System.Data.DataTable GetDataTable(System.String, System.Data.CommandType, System.Collections.Generic.Dictionary`2[System.String,System.Object])

STACKTRACE:    at IUS.SQL.GetDataTable(String strQuery, CommandType Tipo, Dictionary`2 parametros)
   at IUS.VerificacaoCliente.Areas(ProfileCommon Profile)
   at Relatorio_Prazos.Page_Load(Object sender, EventArgs e)
   at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
   at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)


texts: oracle - hashing method, excluding DBMS_CRYPTO

texts:
BACKGROUND:
DBAs have hidden DBMS_CRYPTO package to any role, grantee, and I wonder why. I have to use wrapper instead :(

Is there any way to get the same hash for the text field with the same text? All below methods give different results for the same text as they seem depending on rowid, or something similar:
SELECT
 , utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg))                                                  AS "RAW"
 , rawtohex(utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))                                        AS raw_hex
 , rawtohex(standard_hash(sys_op_map_nonnull(log_msg), 'MD5'))                                       AS md5_hex
 , ora_hash(log_msg)
 , dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))           AS md5
 , rawtohex(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))) AS hex

log_msg is CLOB


texts:
We're using a Vendor App running on SQL Server Enterprise, and it has a rather annoying quirk of executing COUNT statements on the Items table while processing most financial documents (orders, invoices, etc.).

E.g. SELECT COUNT('A')  FROM [dbo].[Items] T0

I'm sure that would normally be fine, but there's over 6 million records, and it takes ~400ms to count them all. This can constitute a substantial portion of the overall processing time.

The table already has an extremely narrow NonClustered Index (tinyint, plus Clustered Key) on it which is what SQL is using when it does the Table Scan, so I don't think we can do any better in that regard.

There's a few solutions I'm aware of, which we'd like to avoid if possible:

Indexed View using COUNT_BIG(*)
Use faster Processors - we're using Cloud Servers, and there's very limited options in this regard.
Deleting Records - not really an option in this case.

Do we have any other options to speed this up?

Here's a Gist showing the setup: https://gist.github.com/elvishfiend/5094f120b14f8ecfb325623edcb5f3eb


texts: I need some help about indexing a JSONB column

texts:
I'm a newbie with PostgreSQL and databases in general. I have PostgreSQL version 9.6.8 and in it there's a schema with JSON data in it.

Some of the fields are being indexed already, but there's a function that selects the data from all 10 of them. The index is this:

    ON "JSON".DATA USING btree
    ((data -> 'field1'::text), (data -> 'field2'::text))
    TABLESPACE pg_default;

If I index all the 10 fields will the select process be faster? From what i know about indexing and databases I assume it should be faster, but I need confirmation.

If indexing all the fields won't help can anyone give me any suggestion what would help make this faster?

The query is

SELECT id, data->>'field1', data->>'field2', data->>'field3', data->>'field4',
 data->>'field5', data->>'field6', data->>'field7', data->>'field8', data->>'field9',
data->>'field10', meta, checksum FROM JSON.DATA;

The column is created as jsonb with:

ALTER TABLE "JSON".DATA ADD COLUMN data jsonb NOT NULL;


texts:
I have a MS-ACCESS database. When i try to do this query:
SELECT *
FROM
(SELECT * FROM tblClients AS a1 LEFT JOIN
(SELECT TOP 1 lVersion, lRequestID AS lVerReqId, lClientId FROM tblClientsVersionsHistory WHERE ID = (SELECT MAX(ID) FROM tblClientsVersionsHistory WHERE lClientId = a1.Id)) AS a2
ON a1.Id = a2.lClientId)

It suggest me to enter a1.Id, but i want to use it from (SELECT * FROM tblClients AS a1).
Thanks in advance!


texts: How do I determine what object two SQL process are contending over?

texts: When long running Query#1 exclusively locks an object (table, index or page), and then Query#2 is executed by another user and becomes blocked on the lock held by Query:1, how would I get the name of the object which the two queries are contending over?


texts: What are the approaches for refreshing materialized views in Oracle, when underlying tables are updated often?

texts:
I am a web developer, maintaining a web app that tracks orders, customers, products, etc, that my client uses internally. I use Oracle 12c, hosted on AWS RDS. My client has just switched some other systems, so we are at a point where data structures have changed, and I am using a new schema in Oracle to store new data in the new structures.

In order that the web app doesn't have to be re-engineered to work with new data structures, a decision was made to implement materialized views in Oracle that union the new data from the new schema (manipulated into the "legacy structure") together with the legacy data.

So now I have to deal with refreshing these materialized views so that the web app constantly has access to the latest data. Ideally the relevant materialized view(s) would be refreshed whenever I receive a new record into the new schema, but during working hours, I receive new data every few seconds maybe. A compromise is OK - if the materialized views are stale by a few minutes (maybe 5 or (less ideally) 10 minutes), that might be an acceptable situation.

My question is, what approach should I have for refreshing these materialized views? I don't want to overload Oracle with constant refreshes, and the web app should provide users with a good user experience when reading/writing data from/to Oracle. I'm far from being an Oracle/DB expert, so I am not really sure what options there are. I guess I could just have a cron job that runs every 5 minutes or something to refresh stale materialized views one by one, but I am wondering if this approach is a bit naive.

In reality, I am dealing with 14 materialized views (for now), and in my testing, some of them take up to 2.5 minutes to do a complete refresh.


texts: Should an update statement query plan ever go parallel?

texts: If an update statement's query plan does go parallel, does it guarantee a communication buffer resource deadlock, or is that determined by whether the parallel plan is using all cores?


texts:
The Application is 24/7 up Full back up time is 4 am and back up size is 92 gb. It takes one hour to complete the back up. During those time users complain that the application freezes.

Just wanted to know whether back up freezes or slows down the application momentarily? No other jobs are running that time.


texts: why HDD is more sensitive to the change of innodb_fsync_threshold than SSD

texts:
What am I doing:
I want to do some experiments to find some interesting facts in DBMS when changing configurations above different physical storage media.
Disk:
Intel P45 SSD vs. SATA HDD 7200 RPM.
Workload:
TPC-C
Performance measurement:
Throughput
Standarized Result when changing innodb_fsync_threshold.
Every data point is repeated 10 times.

Thanks for the help!


texts: Is it possible in SQL Server 2008 to have Structures in one file and Rows Data in another file?

texts:
I know this may sound ridiculous but:
Is it possible to separate the Data Definition (i.e. Structures of my Tables, Views and other things) and Rows-Data on a File-Level in SQL Server.
I think it will be useful to have two or more filegroups on 2 or more physically separated drives and have files in them to solely store the definitions or row-data; so if your row-data HDD crashes, you wont lose the data structure...
Can this be done through Partitioning?


texts: SSIS package deployment. Changing protection level takes a very long time

texts:
We are in the process of migrating our servers to a new data center. Currently we are on SQL Server 2014 and SSIS packages deploy in seconds.

The new data center has several SQL server 2016 installs and I noticed that on some of them the "Changing protection level" takes a very long time (around 7 minutes!) but on some it is fast. When I change the SSIS project's TargetServerVersion to 2016 the changing protection level is slow on all new servers.

The ProtectionLevel of the project and packages is set to (and has been) to DontSaveSensitive.

What is causing the slowness and how can I troubleshoot this? It eventually does deploy without any errors.


texts: SQL Access Denied - GRANT solutions fail

texts:
I've been searching for the solution for quite a while now, however, all the solutions involved another SQL code which would also be denied.

I use PHPMyAdmin from 000Webhost, and when I try to run something to create a table;

#1142 - CREATE command denied to user 'a2299145_admin'@'localhost' for table 'members'

I'm not really experienced with SQL, but I was able to run commands before, and then when I tried to create a new column, it showed the 1142 error.

Thanks in advance.


texts: Does anybody have a recommendation on which performance counters to collect and/or templates to use on the profiler before migrating SQL databases? The purpose is to evaluate the workload and make decision on which cluster to put the databases under. Thank you for all your help!


texts: How to update SQL Server 2005 clustered instances?

texts:
We have a 3 node cluster with an instance running on each.
Windows 2003 R2 32bit (16GB) / SQL Server 2005

I want to bring them all up to build 9.00.5266 (SP 4 + CU3) (1 Node is already updated)
The links and articles I have found haven't been very helpful in terms of order installation and gotchas.

Our test cluster nodes were already at 9.00.5266. (My predecessor did that before I arrived).

I updated the test cluster instances to 9.00.5292 (Cluster only has 2 nodes)
Although I got there, this did not go as expected....

The plan was :

Move all instances to node2
Update the instances on node1
Reboot node1
Move all instances to node1
Update the instances on node2
Reboot node2
Move an instance to node1

I got to step 5. Then the installer said I had to use the active node. That means that the instance must go offline.

Is this a pitfall of SQL Server 2005 or am I doing this wrong?

Why did the first update work?

There are only few hotfixes available after build 9.00.5292 (All via links from MS upon request) so I can't experiment any further without reinstalling the test cluster.

I'm sure there are a couple of DBAs out there that have experience with updating SQL Server 2005 clustered instances. [Smile]

Most helpful links found:

SQL Server failover cluster rolling patch and service pack process(2008+ only. No link to 2005) [Sad]
[How to install SQL Server 2005 SP3]3


texts: How can I quickly detect that connectivity between two linked servers has been restored?

texts:
Two database exist on two different SQL Server instances. They are linked servers, but they cannot communicate due to network issues.

Someone solves the network issues, and connectivity is restored. I want to run a stored procedure as soon as possible after connectivity has been restored. How can I accomplish that?


texts: Optimizer gets the wrong number of estimated rows even with updated stats

texts:
I have a procedure running in SQL Server 2008 that contains a query similar to the one below;

SELECT
/* various columns from table 1 and table 2 */
FROM dbo.tbl_1 AS a
INNER JOIN dbo.tbl_2 AS b ON a.AccountNumber = b.AccountNumber
WHERE a.RecordActive = 1 AND b.RecordActive = 1;

tbl_1 and tbl_2 are both partitioned views that equally contain 3 yearly base tables (2014-2016), partitioned by month and unioned in the view. The tables are large and 2016's data contains approximately 34,000,000 records for the first 3 months alone.

The query normally takes 10 minutes to run, when the right plan is chosen. However, recently it has been taken well over an hour.

When I check the estimated plan, it uses an index seek using a filtered index WHERE RecordActive = 1 for the table containing 2016's data. The estimated rows is 18,000, but when the query executes the actual number of rows is closer to 2,000,000 - a clustered index scan probably being a better choice.

This is the case despite updating the statistics for the filtered index with a full scan. As the predicate on the tables for column RecordActive is a BIT datatype yielding either 1 or 0, the histogram for the filtered statistic only contains one entry for the value 1, which shows the number of rows equaling that value is 2,000,0000.

I have turned on OPTION(RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 9204) and I can see that the filtered stats have been considered. So why then does it get the estimate so badly wrong?

Please help, as I am running out of ideas and would like to know what is really happening under the hood!

P.S. If pictures would help explain the problem better please let me know, this is my first post on the site so please excuse my bad etiquette.


texts: How to setup continuous DB replication from on-prem MS SQL server to PostgreSQL on AWS RDS without reconfiguring source machine?

texts:
I am looking for a way to set up continuous data replication between an on-prem Microsoft SQL Server to AWS RDS PostgreSQL. The source table is updated once every 24 hours. The challenge here is that I don't have any control over the source (on-prem) DB. All I have is a DB user that I can use to query the database. In a nutshell, I am looking for the following requirements:

The source machine should not be modified in any way (i.e. applying configurations to the source machine)
Cheap (do not require a migration server to run 24/7) and easy to manage (set up once and forget it)
Ideally, the solution should only migrate the changes done to the source DB table such as adding/removing records. In other words, avoid emptying the target table and re-filling it with the source table records (that is what the AWS DMS "Full-Load" would do)

I was looking at AWS DMS Ongoing Replication but it requires changes to the source machine so I had to dismiss that solution (see Using a Microsoft SQL Server database as a source for AWS DMS). Also, that solution would require a "Replication Instance" to run 24/7.
Is there anything else I should be looking at that satisfy the above requirements? Or my only option would be to go with "Full-Load" AWS DMS migration which would be potentially costly solution provided the frequency of updates to the source database table?
I would appreciate any thoughts on the problem, please.


texts: What structure is used for storing CONTEXT, CTXCAT and CTXRULE Oracle text indexes?

texts: AFAIK there are three major structure types used for storing Oracle index data: B-tree, R-tree and Bitmap. All indexes use one of these structures. However, not sure about CONTEXT, CTXCAT and CTXRULE Oracle text indexes...


texts: Syntax on Changing return String by Concatenation?

texts:
I successfully created the scalar function FN_TotalBilled in SQL Server, which gives me the total amount invoiced to a company using tables Invoices(InvoiceID, InvoiceTotal,VendorID,..) and Vendors(VendorName, VendorID,.....), and I can get the function to return an integer value, BUT I cannot figure out how to get the function to return a value of the type $Integer. Here is what I have:

CREATE FUNCTION Fn_TotalBilled (@VendorName VarChar (50) )
RETURNS Int
BEGIN  RETURN (SELECT SUM(InvoiceTotal) FROM Invoices I join Vendors V
on I.VendorID=V.VendorID where VendorName = @VendorName ) ; END ;

Now, I can get a return value using:

SELECT dbo.Fn_TotalBilled('CompanyName') ,

Which spits out an integer.

BUT I want as a return to get a string $integer. This is what I am trying,
which is not working:

 Select '$'+ dbo.Fn_TotalBilled('IBM')

I get the error message:
"Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '$' to data type int."

I then tried :

Select CAST('$' AS Varchar) + dbo.Fn_TotalBilled('IBM')

But that did not work out either. Can someone help me get a return string
$dbo.Fn_TotallBilled ?  I also tried to change the function by selecting SELECT '$' + ...

What else can I do?


texts: Unable to access OEM 11g grid control after upgrading to Firefox ver 39.0

texts:
Previously, I have encountered the similar issue after upgrading Firefox to version 37.0.1.

Here's the link of the question posted: Unable to access OEM 11g after upgrading to Firefox ver 37.0.1

I applied the suggested workaround Set Firefox's about:config setting security.tls.version.fallback-limit to '0'. It worked for Firefox version 37.0.1 however, yesterday my firefox was updated automatically to verson 39.0 (I have since disabled the auto update). Everything broken again.

Working around no longer fix. Not sure what Firefox has done with the new release.


texts: MySQL deadlocking because update using full table scan

texts:
I have these jobs that run in parallel that call a series of queries on my DB. They're batched so each job is associated with a group of "bar" objects. So in the example below, temp1 is just a temporary table like:

CREATE TEMPORARY TABLE temp1 (id int(11) NOT NULL, PRIMARY KEY (id))

The problem I'm having is queries like this are deadlocking:

explain update
    foo
join
    temp1 on foo.bar_id = temp1.id
set
    foo.a = null;

Gives the output (please excuse the formatting):

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   UPDATE  foo NULL    ALL foo_60254a58    NULL    NULL    NULL    222939  100.00  Using where
1   SIMPLE  temp1   NULL    eq_ref  PRIMARY PRIMARY 4   db.foo.bar_id   1   100.00  Using index

So as I understand it, this will lock every row while updating because of that second line. As a sort of band aid, if I put the IDs to update in a temp table everything works fine and the deadlocks stop:

create temporary table temp2 as
(
    select
        foo.id
    from
        foo join temp1 on foo.bar_id = temp1.id
);

explain update
    foo
join
    temp2 on foo.id = temp2.id
set
    foo.a = null;

Gives:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE temp2    NULL    ALL NULL    NULL    NULL    NULL    1135    100.00  NULL
1   UPDATE  foo NULL    eq_ref  PRIMARY PRIMARY 4   db.temp2.id 1   100.00  NULL

So not only does this fix my problem in practice, that result also looks a lot better.

This is where my MySQL knowledge fails me. I don't understand why the first query is doing that full scan. I thought it was because it's not using an index but I tried stuff like force index for join (foo_60254a58) to no avail.

Is there something better I can do here? I'd at least like to understand what the problem is. Thanks!

Update: foo has 222,939 rows (seen in EXPLAIN output above). Temp table has 5000 rows. bar has ~483,000 rows.

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bar_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_60254a58` (`bar_id`),
  CONSTRAINT `foo_60254a58` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`)
);

UPDATE 2:

So I was able to fix this by doing two things. First changing the first query to:

explain update
    temp1
straight_join
    foo on foo.bar_id = temp1.id
set
    foo.a = null;

And second, I had to recreate the bar_id index. For some reason it was stuck at cardinality 1 and wasn't doing anything. This part I do not understand.


texts: How does the correlation statistic parameter affect the cost of index scan?

texts:
I have the table customers having customerid SERIAL as a primary key. Initially I had the following statistic for that column:

  attname   n_distinct    correlation
customerid       -1         -0.393365

The following query

EXPLAIN ANALYZE SELECT * FROM customers WHERE customerid < 10

produced the following analyzed plan:

Index Scan using customers_pkey on customers  (cost=0.00..35.46 rows=9 width=268) (actual time=0.003..0.010 rows=9 loops=1)
  Index Cond: (customerid < 10)
Total runtime: 0.029 ms

Note, that the cost to get all rows is 35.46. Now, I ran

CLUSTER customers USING customers_pkey;
ANALYZE;

and the correlation became equals to 1. After running the query once again I got the following analyzed plan:

Index Scan using customers_pkey on customers  (cost=0.00..8.41 rows=9 width=268) (actual time=0.003..0.005 rows=9 loops=1)
  Index Cond: (customerid < 10)
Total runtime: 0.024 ms

Note that the cost is decreased more than 4 times (8.41 now). My question is how exactly the cost of ranged index scan depends on the correlation? How is it computed? It would be good if you pointed out to some references to the postgresql documentation.


texts: How to send a parameter to a trigger function from an external resource

texts: I have a trigger that executes before delete to audit changes in tables. The problem I have is that one of the fields I save in the xtable_audit table is the user id (of an user table) that is used in the web application. How can I send that external iduser from the web application to the trigger function when a delete occurs in any table that has that trigger?


texts: SSRS 2016 Datasource - SQL Server 2000 database

texts:
Looking to upgrade SSRS from 2008R2 to 2016.

Can SQL Server 2000 databases be used as data source in SSRS 2016? And are there any other known issues that could come up with the migration?


texts: How can I find out the last time a MySQL table was analyzed?

texts:
I'm interested in this for InnoDB mostly, but also for MyISAM.

I looked around in information_schema and did not see this data anywhere.

I'm using MySQL 5.5.16.


texts: MongoDB: is order guaranteed deep into the aggregation pipeline?

texts:
I'm trying to get the "top" 2 items for any number of foreign keys, sorted by some key. What I have is the following, which appears to work:

db.getCollection('col1').aggregate([
    {$match: {fk: {$in: [1, 2]}}}
    {$sort: {name: -1}},
    {$group: {_id: "$fk", items: {$push: "$$ROOT"} }},
    {$project: {items: {$slice: ["$items", 2]} }}
])

My question is: does MongoDB guarantee that the sorting I apply in the second step in my aggregation via $sort will be maintained when I get down to the $slice inside my $project in the last step of my aggregation?

The data I'm seeing indicates that MongoDB is currently behaving this way, but I'm wondering if this is spec'd or if MongoDB is free to return the items array in a different order if the optimizer ever decides it would be advantageous to do so.


texts: Restoring a database using backups that cover the same time periods multiple times

texts:
everyone

I have some questions about a database restore sequence. Suppose I have the following backup events:

t00 Database creation

t01 FULL database backup 1

t02 Database DIFFERENTIAL backup 1

t03 Log backup 1
t04 log backup 2
t05 Log backup 3
t06 log backup 4
t07 log backup 5

t12 Full Database Backup 2

t13 Log backup 9
t14 Log backup 10

t15 Disaster Hits!

Now, suppose I want to restore the database back to the end of log backup 4, what I would normally do is to restore

Full database backup 1,
Log backup 1
Log backup 2
Log backup 3
Log backup 4

I skip Database DIFFERENTIAL backup 1 because I believe it is redundant. But I have seen many articles that would include Database DIFFERENTIAL backup 1 in the sequence.

The question is: Aren't we applying the changes contained in the backups twice if we were to include the differential backup in the sequence?

Is there any difference between these two approaches, either in terms of possible data loss or performance?

The overall question is then: Is it CONCEPTUALLY okay to restore a database using all kinds of backups (Full, Differential, Log, Files/Filegroups) as long as they are restored in order from the earliest to the latest, and the backups span the entire time period required, even though they might containing overlapping information about changes to the database?

Thanks


texts: Safest way to uninstall the previous SQL Server versions

texts:
I'm trying to free up some space in my Testing SQL Server. It has SQL Server 2005, 2008, 2008R2 and the compact or express edition. Since I only use lately the 2008R2 version, its not clear for me from the Program Files which of the older folders I should delete (if I'm doing this right?). I see folders named as (80, 90, 100, MSSQL.1,MSSQL.2, MSSQL.3, MSSQL.4, MSSQL10_50.SQL2008R2...)

I will keep the ones that has the 2008R2 obviously, but is there a better way to not accidently delete something that I'm not supposed to?

I'm using MS.Windows Server 2003 R2 Enterprise Edition. Thanks


texts:
I have a varchar column named [Dato Time] which contains values like:

2011-05-12 00
2011-05-12 01
2011-05-12 02

So, a date and a Hour-specific time.

I would like to split the date and the time up in two columns, a column named [Dato] with datatype date and a column named Time with datatype time(7).

I extracted the date-part with the following substring:

UPDATE Dato_Time SET [Dato] = SUBSTRING([Dato Time], 1, 10)

This worked without any problem. But when I try to run the same code for the time-part:

UPDATE Dato_Time SET [Time] = SUBSTRING([Dato Time], 12, 13)

It gives me the following error:

  Conversion failed when converting date and/or time from character string.

Any ideas?


texts: Incorrect syntax in adventure works install script via SMSS

texts:
Im trying to install a sample DB by script. Ive tried with pubs, northwind and now adventureworks. I get a lot of errors. Im posting here because i believe im doing something wrong. Im assuming the install script for adventureworks is properly scripted still i get lots of error messages like

> Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ':'.

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
    Aug 22 2017 17:04:49
    Copyright (C) 2017 Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 17134: )

Started - 2018-07-07 15:15:49.647

*** Dropping Database
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '$'.

*** Creating Database
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '$'.


texts: Azure Data Sync Between Two Azure Databases

texts: I would like to ask if I use Azure data sync with my two azure databases is there any way that I can still download data tier and install it on my local machine. I am planning to create a sync between some of my tables inside my database A and copy its record to the database B at a specific time daily. is this possible on Azure Data Sync?


texts:
According to this site (http://www.dbspecialists.com/oracle11glinux.html), Oracle is only supported on the following Linux distributions:

Oracle Enterprise Linux
Red Hat
SUSE
Asianux

Is there any technical reason that Oracle would perform poorly on a Debian-based install (Ubuntu)?  Or is this more about dealing with Oracle from an enterprise support standpoint?


texts: Do I need a Primary Key if it's already an Index

texts:
I have a bunch of tables with no Primary Key. Some do already have a non clustered index comprised of what would be the Primary Key (ClaimID, LineNBR). The columns used are currently set to accept NULLs so i would need to alter that. Is there value in creating a Primary Key for this and would that make having the non clustered index on the same columns superfluous?
Is it best to leave them with just the non clustered index, delete the non clustered index and make it a Primary Key, or add the Primary Key AND keep the non clustered index?


texts:
I get the following error when I try to use mysqlcheck, mysqlrepair or mysqlanalyze:

mysqlcheck: unknown variable 'max_allowed_packet=100M'

I am trying to find whether there are any corrupt tables or indexes.

Background:

MySQL  Ver 14.14 Distrib 5.5.24, for Linux (x86_64) using readline 5.1
I used the command mysqlcheck  -u xxxx -h xxx.xxx.xx.xx -d xxxx -p xxxxx

my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set = utf8
max_allowed_packet = 100M

Under [client]


texts:
I have an alwayson cluster which was already built when I joined the company. Both the nodes Have Data and log drives but no dedicated Backup drives. The Veeam application which resides in this cluster is not growing much in terms of database size and is being backed up to the data drive as of now.

We are planning to add two more applications to this cluster. I am thinking to ask for a dedicated backup drive for this. My question is does this 2 node setup require 2 backup drives? One for secondary and one for primary? Please clarify

Thanks!


texts: Promote Postgres standby while materialized view updates in primary

texts:
I have one primary Postgres server (serverA) and one standby Postgres server (serverB), both running Postgres 9.4 with Streaming Replication enabled.

Is it possible to update a materialized view on serverA, promote serverB to primary, and then once the materialized view is updated, promote serverA back to primary and have everything replicate correctly (the updated view to serverB, and any writes on serverB while it was primary back to serverA)?

(Also, just to note, both systems currently receive very minimal production traffic.)


texts: How can I configure a connection string in SSRS 2012 using named pipes?

texts:
I have SQL Sever 2012 and I want to create a data source that uses named pipes rather than TCP.

This is when I have tried:

Data Source=np:\\.\pipe\MSSQL$MSSQLSERVER02\sql\query;Initial Catalog=cleverdataout

If I test the connection, I get an error message, with error code 25, indicating that the connection string is not valid.

Is there a way to use named pipes? Or am I forced to use TCP?


texts: Is MySQL Cluster just for in-memory databases?

texts:
In MySQL website I see below definition for MySQL Cluster.

  MySQL Cluster is a technology that enables clustering of in-memory
  databases in a shared-nothing system.

I want to use Clustering for a chat application like viber that there are some Ejabberd servers, which use MySQL as database.Is it necessary that  databases use in-memory tables. according this definition all databases and tables should be in-memory or not?

This is the link of MySQL Cluster definition.


texts: I can open a connection with SQL Developer but when i do the same with .NET returns me ORA-12514

texts:
In SQL Developer

In my .NET proyect, Web.config:

<add name="DBConection" providerName="Oracle.ManagedDataAcess.Client" connectionString="Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.27.32.27)(PORT=1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME=BDTEST1)));Password=pass;User ID=user;" />

When trying to connect with the application it gives me:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I have modify all TNSNames.ora files I could find in my pc, but the database is in another server that i cannot acces. Is the problem in my PC or is something wrong in the server that impede this connection?

EDIT:

This is the TNSNames.ora, in fact, all the TNSNames.ora I could find in mi PC, because I don't know what is the file the application is using, or if it is even in my pc.

    BDTEST1 =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.27.32.27)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BDTEST1)
    )
  )


texts:
I am first timer with ms access and also here

I am working on a database of library.  I have two different tables which stores details one for books and others for Periodicals.

How can make accession number primary key for both tables.

Are autonumber a accession number when a new entry made in books are periodicals.


texts:
My question is this:
When running sp_blitzindex, I get a lot of these 2 warnings/suggestions:

  Index Hoarder: Unused NC index

and

  Feature-Phobic Indexes: Potential filtered index (based on column name)

With index hoarder, these are indexes that I've built against a FK column.
I thought somewhere I read that was a good idea to have.

With feature-phobic, I had indexes set up to use these cols (where clause in the index definition), but when using sp_blitzindex, I got a lot of recommendations for indexes that didn't use the feature. (It either stuck the bit field in the includes or in the index itself.)

What are your thoughts on whether or not to keep the indexes on the FKs?
And, where do you think I could get the most bang for indexes (features or results of your blitzindex?)

Right now this is being used against an OLTP system that has some flat tables used for searching.


texts:
Install MySQL5.5 in CentOS7 by following instructions on this page: A Quick Guide to Using the MySQL Yum Repository

After that start the service: service mysqld start

This returns the error:

Job for mysqld.service failed because a timeout was exceeded. See "systemctl status mysqld.service" and "journalctl -xe" for details.

Below is the mysqld log file:

[root@iZu1a81d1qsZ log]# vi mysqld.log
160513 00:11:16 mysqld_safe Starting mysqld daemon with databases from     /var/lib/mysql
160513  0:11:16 [Note] /usr/sbin/mysqld (mysqld 5.5.49) starting as process 15341 ...
160513  0:11:16 [ERROR] An old style --language value with language specific part detected: /usr/share/mysql/
160513  0:11:16 [ERROR] Use --lc-messages-dir without language specific part instead.
160513  0:11:16 [ERROR] Can't read from messagefile '/usr/share/mysql/errmsg.sys'
160513  0:11:16 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Unknown error 1146
160513  0:11:16 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
160513  0:11:16 InnoDB: The InnoDB memory heap is disabled
160513  0:11:16 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160513  0:11:16 InnoDB: Compressed tables use zlib 1.2.3
160513  0:11:16 InnoDB: Using Linux native AIO
160513  0:11:16 InnoDB: Initializing buffer pool, size = 128.0M
160513  0:11:16 InnoDB: Completed initialization of buffer pool
160513  0:11:16 InnoDB: highest supported file format is Barracuda.
160513  0:11:16  InnoDB: Waiting for the background threads to start
160513  0:11:17 InnoDB: 5.5.49 started; log sequence number 1595675
160513  0:11:17 [ERROR] Aborting

160513  0:11:17  InnoDB: Starting shutdown...
160513  0:11:18  InnoDB: Shutdown completed; log sequence number     1595675
160513  0:11:18 [Note]
160513 00:11:18 mysqld_safe mysqld from pid file     /var/run/mysqld/mysqld.pid ended

My question is: what causes this? How can i fix it?


texts: Logging errors with extended events excluding some users

texts:
I use extended events to store database errors, like this:

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        [severity] >= (11)
    )
)
ADD TARGET package0.asynchronous_file_target
(
    SET filename='J:\ServerXmlOutput\ErrorCapture.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

This works.
I have some users called test1, test2 and so on that work interactively, and produce several errors. I don't want to log these errors.
I tried to change the WHERE clause like this:

WHERE
(
    [severity] >= (11) AND sqlserver.username NOT LIKE 'test%'
)

I get these errors:

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'LIKE'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'TARGET'.
Msg 319, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

How can I exclude the errors from some users from my logging?


texts: Select Items that has one item but not the other

texts:
I have three tables:

Table1:

ID      Name
1       Item1
2       Item2
3       Item3

Table2:

ID      Name         Type
1     Logistics       One
2     Human Resource  Two
3     Finance         Three

Table3 (Many to Many Table):

ID     Table1ID      Table2ID
1         1             1
2         1             2
3         2             1
4         3             1
5         3             2
5         3             3

I want to select all the items from table1 that exist in many to many table for just type one from table2 and not to the type two. the type three is not concerned. So all the items from table one that has a record in table3 for only type one and not two.


texts: Indicators for right balance of the number of DB2 instances per AIX LPAR

texts:
I have a question that has been bugging me, but I just don't know how to validate it. We run DB2 LUW on AIX in our shop. Our AIX is currently at 7.1. Our DB2 is at 9.7 FP 4 for most installations and 10.5 FP 2 for our newest databases. We even have mixed installations (ie, 9.7 and 10.5) on the same "boxes".

Our boxes are LPARs of course, given to us by our AIX admins. The AIX admins are also our storage admins and so all disk is SAN. There is not much physical about our database servers.

What has been bugging me is what is the correct balance of DB2 instances per LPAR? I have started to see some strange behavior regarding DB2 auto-configuring itself. On one DEV LPAR we have the following configurations:

Do note that the following is true for all instances: HEALTH_MON is set to OFF, and AUTO_MAINT is set to OFF for every database. We do allow Self-Tuning Memory Manager (STMM) to be on by default with all necessary parameters set for that (ie, zeros or AUTOMATIC in the correct parameters, the default that DB2 provids...). If we build an OLTP database then we set instance parm MAX_QUERYDEGREE to 1 and leave INTRA_PARALLEL to NO and db parm DFT_DEGREE to 1. If we build an OLAP database (separate instance) we set instance parm MAX_QUERYDEGREE to ANY and INTRA_PARALLEL to YES and the db parm DFT_DEGREE to ANY. We also do not try to mix OLAP and OLTP databases in the same instance, though part of the question is whether to mix the instances on the same LPAR.....

Here is what we have on one DEV LPAR:

First instance is 9.7. Contains some OLAP databases, though they are very small since they are used for testing out DDL changes.
Second instance is 9.7. Contains an OLAP database "playground" for developers. (This one is supposed to be retired the first chance we get.)
Third instance is 10.5. Contains the official DEV version of that playground database from instance 2.
Fourth instance is 10.5. OLTP database with a home grown metadata dictionary.
Fifth instance is 10.5. OLTP database that is used for lookup data.
Sixth instance is 10.5. ODS database (unsure if this is OLTP or OLAP....) for developers to tie data from two transactional systems.

Now disk isn't the biggest issue as that is handled by the SAN. And even memory and CPU are virtuallized by nature of this being an LPAR. But I have seen some strange stuff with the memory.

The one OLAP database (both the playground and the official DEV) are rather large. The developers are testing ETL'ing in large volumes of data and estimating size for their official request for QAT and PROD buildouts. Ever since one of these has started to grow, I've noticed that new instance buildouts and new database buildouts now have STMM turned off by default. I can manually turn it back on, but the point being I find this disturbing. Whether or not STMM is enabled by default is decided by DB2 at creation time based on system resources. So, to me, this is saying DB2 does not feel it has adequate resources for auto management, so it is trying to cap memory in hopes of it gains at least that much. (My guess on that.)

When I discuss this with the AIX admins, they don't seem to think memory and CPU is an issue. They say we aren't really fulling utilizing what we could be, so in their mind (and I'm saying this very loosely) we could put all our dev instances on the same LPAR. Or similar for other environments. Again, that is loose, because they understand the point of separation for HA and DR scenarios.

Anyway that leaves me wondering, are their any pointers or guidelines between knowing when you can add instances to an LPAR or when you should split to other LPARs? I'm sure the answer is "it depends", but I'm looking for any approximations or guidelines? Especially if those differ along the lines of OLTP and OLAP.


texts: Script to install Oracle client 12c2 on Redhat linux 7.5

texts:
Can someone help me to provide a script which I can use to install the Oracle client as part of the server build or AMI. Below script I was able to pull out from  the guide in the Oracle site. But still I am missing something. I have added the main code area. Prerequisites are taken care.

By this code doesn't work if it is executed as script, due to user change to oracle just before execution of runinstaller and once that is done it continues to throws. But required permission are already granted. Someone please help.

[INS-30131] Initial setup required for the execution of installer validations failed.
   CAUSE: Failed to access the temporary location.
   ACTION: Ensure that the current user has required permissions to access the temporary location.
*ADDITIONAL INFORMATION:*
 - PRVG-1901 : failed to setup CVU remote execution framework directory "/tmp/CVU_12.2.0.1.0_oracle/" on nodes "ip-10-81-140-134"
 - Cause:  An operation requiring remote execution could not complete because the attempt to set up the Cluster Verification Utility remote execution framework failed on the indicated nodes at the indicated directory location because the CVU remote execution framework version did not match the CVU java verification framework version. The accompanying message provides detailed failure information.
 - Action:  Ensure that the directory indicated exists or can be created and the user executing the checks has sufficient permission to overwrite the contents of this directory. Also review the accompanying error messages and respond to them.
Summary of the failed nodes
ip-10-81-140-134

groupadd oinstall
groupadd dba
sudo useradd -g oinstall -G dba oracle; echo Nbst@AWS01 | passwd oracle --stdin
cd /opt
mkdir app
mkdir app/oracle
chown oracle:oinstall app
chown oracle:oinstall app/oracle
cd /tmp

wget https://amazonaws.com/linuxx64_12201_client.zip
chown oracle:oinstall linuxx64_12201_client.zip
unzip linuxx64_12201_client.zip
chown oracle:oinstall client
cd /tmp/client/
wget https://amazonaws.com/client_install.rsp
chown oracle:oinstall client_install.rsp
chmod 775 client_install.rsp
sudo su oracle; echo Nbst@AWS01 | passwd oracle --stdin
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/12.2.0/client_1
/tmp/client/runInstaller -silent -responseFile /tmp/client/client_install.rsp
exit
cd /opt/app/oraInventory/
./orainstRoot.sh
cd /opt/app/oralinventory/
./orainstRoot.sh

cd /opt/app/oracle/product/12.1.0/client_1/network/admin/
wget https://amazonaws.com/tnsnames.ora
chown oracle:oinstall tnsnames.ora
chmod 775 tnsnames.ora


texts: MySQL optimizing buffer_pool_size among buffer_pool_instances - where is the trade off?

texts:
I'm looking to optimize MySQL INNODB by tuning buffer pool individual instances.

According to the MySQL documentation:

  "The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte."

However, this doesn't specify the optimum approach to defining  innodb_buffer_pool_size. Which of these is considered a better approach:

a) a larger number of smaller buffers (given that each buffer instance is >1GB)

e.g. 45 buffers instances over 50GB buffer pool

or

b) a smaller number of larger buffers

e.g 5 buffer instances over 50GB buffer pool

?


texts: Oracle database uses wrong parameter file

texts:
When I try to startup my Oracle database I get the following error:

LRM-00109: could not open parameter file '/opt/app/oracle/product/11.2.0/db_1/dbs/initjicdbaex.ora'

The /dbs directory in my Oracle home contains the init.ora file I want to use, but I don't know how to point the database to read that file.

How do I set the correct parameter file path to init.ora?


texts:
I have a view in which I need to Group By or SUM OVER Partition By year and quarter, but the year and quarter will be computed within the view. I would like to keep it all within a view instead of needing tables and SPs.

The goal is to get SUMs by of all records within the first quarter of a year, spanning over multiple years. What I have is:

SELECT MIN(PAYROLL)                     AS MinPayroll
      ,MAX(PAYROLL)                     AS MaxPayroll
      ,YEAR(CHECK_DATE)                 AS PayrollYear
      ,MONTH(CHECK_DATE)                AS PayrollMonth
      ,SUM(sit)                         AS TotalStateTax
      ,SUM(FIT)                         AS TotalFedTax
...
...
FROM CHECKS
WHERE (PayrollMonth = 01 OR PayrollMonth = 02 OR PayrollMonth = 03)
GROUP BY PayrollYear

This gives a good idea of what I want to do, but SSMS doesn't like it, saying 'Invalid column name 'PayrollMonth', and 'Invalid Column name 'PayrollYear'

I'll start working on sample data to help, but I wanted to get this out now in case someone knew what to do off the top of their heads without data.


texts: Is this an appropriate forum for help with beginners dba questions?

texts:
I've installed MAMP and Wordpress on a local machine to learn about databases.

Can anyone recommend a 'hello world' type tutorial, where I can create my own database within this environment?

I would like to make a database to track who I loan things to.. so I imagine starting with two tables:

users
    userId
    name
    phone
    email address

assets
    assetId
    make
    model
    serial
    borrowers_id

If there is a more appropriate forum for these, please let me know!  Thank you in advance, this is my first post on stackexchange.


texts: Unable to CREATE DATABASE: error while processing file dmanage.bsq

texts:
I want to create a DATABASE on my recently installed Oracle 12c so, after creating spfile and listener/tnsnames/sqlnet.ora for my instance, I call my CREATE DATABASE request :

CREATE DATABASE DBNAME
   USER SYS    identified by password
   USER SYSTEM identified by password
     MAXINSTANCES  1
     MAXLOGHISTORY 1
     MAXLOGFILES   20
     MAXLOGMEMBERS 4
     MAXDATAFILES  200
   CONTROLFILE REUSE
   DATAFILE
           '/[...]/system_01.dbf' SIZE 1000M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE
           '/[...]/sysaux_01.dbf' SIZE 2000M REUSE
   UNDO TABLESPACE "UNDO" DATAFILE
           '/[...]/undo_01.dbf'     SIZE 2000M REUSE
   DEFAULT TEMPORARY TABLESPACE "TMP" TEMPFILE
           '/[...]/tmp_01.dbf'       SIZE 2000M REUSE
            EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 1M
   LOGFILE
       GROUP 1 ('/[...]/redo_1a.dbf',
                '/[...]/redo_1b.dbf') SIZE 100M REUSE,
       GROUP 2 ('/[...]/redo_2a.dbf',
                '/[...]/redo_2b.dbf') SIZE 100M REUSE,
   CHARACTER SET WE8ISO8859P15
   NATIONAL CHARACTER SET AL16UTF16 ;

And I get this error:

CREATE DATABASE DBNAME
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dmanage.bsq' near line 22
ORA-00604: error occurred at recursive SQL level 1
ORA-19502: write error on file "/[...]/sysaux_01.dbf", block
number 640 (block size=8192)
ORA-27085: Message 27085 not found;  product=RDBMS; facility=ORA
Additional information: 1044480
Additional information: 1048576
Process ID: 23040
Session ID: 244 Serial number: 9021

The request at the line 22 of the dmanage.bsq file is:

CREATE TABLESPACE sysaux DATAFILE "D_SXFN"
  "D_SDSG" ONLINE FORCE LOGGING
/

What is the problem with my request?


texts:
We have a large UPDATE (affects about 200K records) on a huge table (80M records).
Due to a coding mistake, the UPDATE updates the same set of rows (200K) all over again with the exact same value.

There is a temporary index (say, TMP_INDX) on the column that the UPDATE updates. [This column is also used in the WHERE clause]. This index is "temporary" in the sense it is created at the beginning of the script and dropped at the end.
Now, the UPDATE runs as part of daily batch and is fast on all days except on one day of the week where it takes almost 10x more time than the rest of the days.
AWR shows there is a 1000x times increase in the physical writes to TMP_INDX on the day the update takes lot of time. This is the only thing that seems to change in the AWR. Strangely, AWR shows "dbfile sequential read" to be the culprit instead of "physical writes" on the days this happens. The execution plan is still the same as the other days.

Given the above input, I am thinking the slow UPDATE might be the result of the increase in the physical writes to the index caused by the update to a column that is part of a composite index. However, as I mentioned earlier, the column is updated with the exact same value, in which case oracle seems to do redo optimization which will not update the index. I could not understand why this would fail on one particular day of the week. Can somebody throw some light into this? Thanks.


texts:
When a symmetric key is created we have the option to encrypted it by using at least one of the following: certificate, password, symmetric key, asymmetric key or EKM.

When a password is used, it it said that:

  When a symmetric key is encrypted with a password instead of a
  certificate (or another key), the TRIPLE DES encryption algorithm is
  used to encrypt the password. Because of this, keys that are created
  with a strong encryption algorithm, such as AES, are themselves
  secured by a weaker algorithm.

So, using password is not best way to protect it. I was not able to find any pros and cons for using the other techniques.

Can anyone tell is there are difference from security or performance matter or it is just a matter of choice?


texts: How do I filter null values from a query?

texts:
I am trying to filter the null values from the following query and it keeps giving me 2 records instead of 1.

Here is my base query

The following query brings me back 2 results and I only need to see one result as there is only one vender for vendor_id 91.  it's giving me a record for the null and an 'N' for the result of my case statement.

SELECT CASE WHEN vs.User_Data_Field1 = '14' THEN 'Y'
            WHEN vs.User_Data_Field2 = '14' THEN 'Y'
            WHEN vs.User_Data_Field3 = '14' THEN 'Y'
            WHEN vs.User_Data_Field4 = '14' THEN 'Y'
            WHEN vs.User_Data_Field5 = '14' THEN 'Y'
            ELSE 'N'
       END USER_DATA_FIELD
FROM RHOP..EDSLKP.VENDOR_SITES VS
where vendor_id = '91'
GROUP BY VENDOR_ID,VS.USER_DATA_FIELD1,VS.USER_DATA_FIELD2,VS.USER_DATA_FIELD3,VS.USER_DATA_FIELD4,VS.USER_DATA_FIELD5
ORDER BY VENDOR_ID

My question is, How do I code this to only bring back one 'N' for the one vendor?

Thanks for any help


texts: MySQL Continue Handler Problems inside a Function

texts:
I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I still end up with a warning "No data - zero rows fetched, selected, or processed". My code is below, what am I doing wrong? I really want to get rid of this warning :)

DROP FUNCTION IF EXISTS `getCompanySetting`;
DELIMITER |

CREATE FUNCTION `getCompanySetting`(setting_company_id INT, setting_name VARCHAR(255))
    RETURNS TEXT
BEGIN
    DECLARE setting_value TEXT DEFAULT NULL;
    DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET setting_value = NULL;

    SELECT
        value
    FROM company_settings
    WHERE
        company_id = `setting_company_id`
        AND
        name       = `setting_name`
    INTO setting_value;

    RETURN setting_value;
END|

DELIMITER ;

mysql> SELECT getCompanySetting(24, 'observers_active');
+-------------------------------------------+
| getCompanySetting(24, 'observers_active') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Update: MySQL Version 5.5.25


texts: PostgreSQL Trigger to override insert on table with inheritance

texts:
We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the correct child table and then return NULL to abort the pipeline so it is only inserted into the child table, though (you guessed it), this makes inserting documents a pain as you have to re-query for the created ids. We've looked into using an INSTEAD OF trigger type, but they cannot be applied to tables, only views.

Here is our current function used by the trigger:

CREATE OR REPLACE FUNCTION create_article_partition_and_insert() RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    partition_date TEXT;
    partition      TEXT;
    resultId       BIGINT;
BEGIN
    partition_date := to_char(NEW.updated, 'YYYY_MM');
    partition := TG_RELNAME || '_' || partition_date;
    IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = partition)
    THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' (LIKE ' || TG_RELNAME || ' INCLUDING ALL) INHERITS (' || TG_RELNAME ||
                '); ';
    END IF;
    RAISE NOTICE 'Inserting into %',partition;
    EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING id;'
        INTO resultId;
    NEW.id := resultId;
    RETURN NEW;
END;
$$;

Is there a better way to partition inserts by time and return the inserted row of the child table without also inserting into the parent table?

Using PG version 9.6.11.

Thank you!

Cross-posting from SO


texts:
I have a query below.

The SUM should be the SIX_AGO + FIVE_AGO + FOUR_AGO + etc.

But the SUM give all the times wrong numbers.

Is there anything I am doing wrong?

 SELECT
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS SUM,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 36 - diff) then 1 END) AS SIX_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 35  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 29 -  diff) then 1 END) AS FIVE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 28  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 22 -  diff) then 1 END) AS FOUR_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 21  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 15 -  diff) then 1 END) AS THREE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 14  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 8  -  diff) then 1 END) AS TWO_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 7   -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 1  -  diff) then 1 END) AS ONE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') -        diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS NO_AGO
FROM TEST


texts: Monthly backup of SQL server DB to PostgreSQL?

texts:
The company I'm working for has a SQL Server with read-only access. They use Crystal Reports hooked up to PostgreSQL for reporting. Is there any way to make it so I can move all the data from the MSSQL DB to PostgreSQL without user interaction? That seems to be the caveat to what I'm trying to do. They need to be able to run this report after I leave without having to interact with it during the process.

Or am I looking at this the wrong way? Is there a way to save a "snapshot" of the SQL Server DB that can be manipulated in Crystal Reports? The ultimate goal is that since the DB is dynamic we need to be able to have a static DB at the end of the month that all the reports can be ran on without having to worry about it changing.


texts: Composite vs auto incrementing integer as Primary key

texts:
I am designing a MySQL 5.7 database for a mobile platform. This platform has activities that users can add to 'to-do' and 'completed' lists.

I have 3 tables, one for activities where it has an auto incrementing primary key called 'activityID' and two tables for each list. Users are not stored in the MySQL DB for several reasons but have a GUID to identity them.

I only need to make 2 fairly simply select queries per list table:

Select/return all activity id's for a specific user in a list
Select/return all activity rows for a specific user after joining the activity table and whichever corresponding list table.

I've researched on stack exchange and elsewhere but have found conflicting answers on the following:

Should I use a composite primary key of the userID + activityID for the list tables, or just use an auto incrementing int as the primary key?
In either case, should I also have activityID as a foreign key to the activity table for each list table? (My understanding is I should to increase data integrity, and to take advantage of cascading delete if needed in the future)

Any suggestions for this is greatly appreciated, I know these things can be difficult to change once the DB is live. Thanks so much!


texts: mysql (5.5.) cmd unresponsive when selecting a huge db

texts:
I had initiated on Friday night a huge (~800G) dump from my machine to a remote host;

Today, (although the command does not seem to have returned yet), I am able to log in to the machine and mysql cmd but when selecting this db, the shell becomes unresponsive:

mysql> use my-huge-db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -

_

How to debug this?

Is there a way to check if the import is still taking place?


texts: How to limit PostgreSQL INSERTs to assure that the caller has an attribute conistent with the parent table?

texts:
In the parent table, there is a column that defines the 'owner' of the row. When inserting into the child, the caller provides an owner id or '%', to indicate that the the caller is the administrator. I was expecting the insert with this check to be slower that a straight insert, but I didn't expect a 70x penalty. Can you give me some ideas for how to optimize the performance to achieve the same result as this statement?
INSERT INTO child (parent_key, value1, value2)
  SELECT $1, $2, $3 FROM parent
    WHERE parent_key = $1
      AND owner LIKE $4
    LIMIT 1;

Table definitions:
CREATE TABLE parent (
  parent_key VARCHAR(255) PRIMARY KEY,
  owner VARCHAR(255)
);

CREATE TABLE child (
  child_key SERIAL PRIMARY KEY,
  parent_key VARCHAR(255) REFERENCES parent,
  value1 VARCHAR(255),
  value2 VARCHAR(255)
);

I ran an explain on my statement, and this is what I see.
 Insert on child  (cost=0.42..8.46 rows=1 width=1670)
   ->  Subquery Scan on "*SELECT*"  (cost=0.42..8.46 rows=1 width=1670)
         ->  Limit  (cost=0.42..8.44 rows=1 width=296)
               ->  Index Scan using parent_pkey on parent  (cost=0.42..8.44 rows=1 width=296)
                     Index Cond: ((parent_key)::text = '111'::text)
                     Filter: ((owner)::text ~~ '%'::text)

Since parent_pkey is a unique index, I would expect the LIKE filter to contribute an insignificant amount to the execution time. This conditional INSERT takes >70 times as long as an INSERT of VALUES. What would be a more efficient way of enforcing this constraint?


texts: Using postgres with the command line without pgAdmin

texts:
I started working with PostgreSQL and till now I only had issues with it ( pgAdmin3 issues the most ) now I work a lot with MySQL and sometimes on the command line by just doing

mysql -u ... -h localhost -p for example and I'm connected through the command line.

Is there also a way to connect to the PostgreSQL database without pgAdmin and run it through the command line (Terminal) (I am working on OS X Mountain Lion).

p.s. I am also not a real big fan of letting PostgreSQL create a user. It annoys me when I start up my Mac and see PostgreSQL as user in the login screen. Is it possible to avoid that?


texts: When did sqlmag.com cease to exist, please?

texts: It obviously has been a while since I visited the sqlmag.com website.  I found it no longer exists but it appears that ITProToday owns it.  Can anyone tell me when this happened, please?


texts:
We currently have a front end application that is business critical and uses Microsoft SQL Server as the database provider. I have been asked to create another system that mimics some of the functions of this application and will insert/modify data in exactly the same way.

The only reason we are looking at doing this custom is because the developer does not provide any sort of API or assistance in manual data entry of their tables. Please note, we own the databases and all the data on the system but we still need the application to perform the day to day tasks.

I am looking for ways to track all changes made in a database (insert/update/delete) on all tables. I have a fresh copy of the system and a blank database to test, to give an example, I would generate a Purchase Order and then find out which tables were modified and what data was added so I could replicate in my code.

I know SQL has Data Change Tracking; however, I cannot see a way to easily implement this on all columns and tables within a database.

I am interested in any recommendations of third party applications or a walkthrough on how to utilise Microsoft's built in functions, appreciate the help if any.


texts: Different results rebuilding an index online and offline

texts:
I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.

When I run the same rebuild index offline, the average fragmentation is 25%, with 4 fragments and 28 pages.

I think the FILLFACTOR is 90. The database is 77GB.  I'm not a DBA or similar, I'm a C# dev, so I'm not fully familiar with all the terms. There is no activity on this table, this is occurring in our Dev environment.

This is the query, with names redacted.

ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON,
    ONLINE = ON,
    SORT_IN_TEMPDB = ON
);

What could be causing this difference? The same situation occurs on multiple tables.


texts: Steps or evaluation order of the SQL statement

texts:
How the SQL statement containing simple statement like

Select x
From y
Where z

Having, order by , group by etc. are evaluated.( In which order they are evaluated.?)


texts: Postgis distance filter does not work as expected


texts: Different plans on Readonly Secondary Replica

texts:
We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose.

One of the reports intermittently runs for much longer than normal.  I can see when it runs slowly the plan is taking a different route and causing a clustered index scan on a big table.

However, when I run the same query in primary replica it runs fine and generates a plan that is what we expect.

Why does it generate a good plan on the primary replica but a bad plan on the secondary? what are the possible reasons?

The servers have exactly the same hardware and the configuration is the same.


texts: SSDT Schema Compare fails when comparing my DB Project to my Azure Server

texts:
I have a SQL Database project that I have built our enterprise DB on. It has deployed several times on internal and AWS hosted SQL servers using SSDT's Schema Compare tool.

The problem when I post to Azure Hosted Win 2012 Server running SQL Ent 2012 sp2. It comes back with "Comparison complete. No differences detected."

I know this is wrong, because I can open Enterprise Manager and compare the schema to the SQL Project and see that there is a difference.

I found several articles talking about how the 2014 release broke the tool, but those were in version differences.

[Yes I did Google this. Stating because I am notorious in forgetting to do so. ] https://www.google.com/webhp?ie=utf-8&oe=utf-8#q=ssdt+data+compare+fail+to+detect+difference&start=10

Other things I have checked include making sure my DB account has unlimited access. I can connect with Management console. I can connect with local programs.

Last confirmation that there was a problem:

I created an SP that has a single return of the number 1.

It could have a return of nothing for the sake of testing.

After I created the SP, I ran schema compare on all of my instances and all but the Azure server showed the variance.

Update

I have verified that this is explicitly with the server, in that now two different users on two different computers are having the same exact problem.


texts: Does it make sense to create index with all columns included

texts:
We have a table with columns

 ID
 OtherTableId
 DataA
 DataB
 DataC
 DataD

Does it make sense to create index on OtherTableID INCLUDE (ID, DataA, DataB, DataC, DataD) when we need all columns, or is it enough to create index just on (OtherTableID)?

I know that index with include will effectively duplicate table size, but will it give performance improvement?

Query is:

select * from Table Where OtherTableID = ?


texts: Why does this SQL Server execution plan contain a snapshot cursor for a select statement?

texts:
Here is the situation. I have a fairly simple query. It returns ~22k rows:
select * from tbl_A
When I run this query in SSMS (2017 7.6), I get a fairly simple plan:

SELECT <= Clustered Index Scan (PK_tbl_A) (Cost 100%)

However, SQL Server 2014 has has cached this plan for this same query:

DECLARE CURSOR (Snap Shot) <= Populate Query <= Clustered Index Insert [CWT_PrimaryKey] (Cost 99.1%) <= Sequence Project <= Segment <= Clustered Index Scan (PK_tbl_A) (0.9%)

                             \___ Fetch Query <= Clustered Index Seek [CWT_PrimaryKey]

Sorry for the formatting, I had to obfuscate some information, so I didn't want to "paste the plan".
I did some research and found the CWT table is the Cursor Work Table in the tempdb.
The only thing I have found "odd" about tbl_A is that there are a mix of char LineOfBusiness char(2) and varchar columns like Manager varchar(255). There is one constraint, a default on one column. No triggers. A primary key on an autoincrementing int column.
I am wondering why this query is using the tempdb and cursors for what seems to be a 100% hit on the clustered index. Why is SQL generating a Cursor Work Table, indexing it and spending most of the query time updating the index in the CWT--when it could scan the actual table index?
Why is my query using cursor that seems to be eating a huge amount of I/O in the tempdb, when I run it in SSMS it does an index scan? Could there be another query, with a cursor, using this plan--which is not shown in the cached plan?
I know I can seek out the source of the query and use index hints, but should I need to? I'm more interested in the circumstances where SQL Server would create a cursor for a straightforward select statement. Also note that I have inherited this table from a legacy system and I am not easily able to modify it.
Any ideas would help. Thanks!


texts: A low cardinality column index and SELECT

texts:
Suppose that a long (1000000 entries for example) MySQL table t has a column c with less than 10 distinct TINYINT values.

Suppose also that most (say 99% or even 99.9%) of these values are equal to zero.

Will adding an index on this column speed up queries like the following?

SELECT * FROM t WHERE c > 1


texts:
I have this table
CREATE TABLE public.element (
    id serial NOT NULL,
    elementname varchar(25) NOT NULL,
    element_sequence int4 NOT NULL,
    element_type varchar NOT NULL,
    creationdate timestamptz NOT NULL,
    validitystart date NOT NULL,
    validityend date NOT NULL,
);

What i need is to group by element_name and element_sequence and extract only the newer of every element.
So for example in a table like this
id|element_name|element_sequence|element_type|creationdate       |validityend|validitystar
--|------------|----------------|------------|-------------------|-----------|-------------|
 1|ab>cd       |               1|simple      |2020-06-18 09:09:00| 2020-06-20|   2020-06-18|
 2|ab>cd       |               1|simple      |2020-06-19 10:02:13| 2020-06-20|   2020-06-18|
 3|ab>cd       |               2|composite   |2020-06-19 10:02:15| 2020-06-20|   2020-06-18|

only id 2 and 3 should be extracted because 1 and 2 have the same element_name and the same element_sequence but id 2 is newer
Real table have more column, but for this example i used only the main


texts: Update a value after an hour when a record is inserted

texts:
I am doing a booking system for an iPhone app and I want to update the Status value to "Completed" after 1 hour.

I initially wanted to check for the TIMEDIFF of the current datetime and StartDateTime to get the remaining time, and update the value through the app once the timer is completed instead. But, I am not able to create the timer when the application terminates, so I need to create it in the database instead.

I'm not sure whether this is achievable, can anyone please help me?


texts: Error when trying to access linked server from another machine

texts:
I have created a linked server from a Excel source and when I query or test the connection from the locally from the server everything is good but if I try the same from another machine with DBMS I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

The test connection to the linked server failed.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Source" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Source". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Server: Windows 2008
SQL: Server 2008 R2

The SQL service is running fine and the user attached has full administrator access.

Any help is appreciated.


texts: Database Design where there's three parent tables and only 1 child table

texts:
Below is a simplified version of my current tables. Each test type is a type of test (eg: one table is for different appearance tests, ie substance is yellow and sticky, or substance is green and dry, where as another table might be for microbial counts in food products).

A result set is just a group of results for each test. So a test can be tested 5 times in one batch, so that would be a result set for a test that has 5 results.

What I really want is just one result set table and one results table as right now it's overly complicated and there's duplication. How do I do that when there's three different types of tests? Ultimate the results can be boiled down to a numeric value OR a pass/fail value.

I could have three columns in the result set table (Type1TestId, Type2TestId, Type3TestId) and populate whichever the result set belongs to but that seems ugly. I could have a new parent table called Tests and from there have a link to the TestType tables and each Test in TblTest would link to one of the three test type tables?

All my solutions seem ugly, so I'm asking for help.

I could do something like this?


texts: query all audit entries defined with 'audit' command

texts: what is the command to list all audit entries defined with audit command? Is there any command for it or maybe tableName to query from?


texts: Access 2007 Work in a separate file using ACCDE

texts:
Have been working in an Access 2007 application for over 3 weeks and it should be ready now. Anyway, I know (and tried) that "Make ACCDE" can actually split the raw data from the application itself so you can run it using the Access RunTime.

The question is... can I deploy my app for general use and modify a separate copy of the forms/views inside the application to deploy it when it´s ready without touching the data?


texts: mysql triggers to multiple table and insert new id to table

texts:
I have 4 table:

table a (id (auto_increment), id_b , id_c, id_d(foreign_key from table b,c,d) , column)
table b (id auto_increment, column)
table c (id auto_increment, column)
table d (id auto_increment, column)

when there's event table a, b, c, d inserted, must copy to table a, b, c, d in other database. (note: in other database table a,b,c,d already has data, so auto_increment isn't identical to current database)

I want to create triggers for that, but I'm confused how can I fill id_b, id_c, id_d from table b, c, d in other database


texts: Can't solve MongoParseError: Invalid connection string

texts:
I'm not able to solve MongoParseError.

First my I tried this

mongoose.connect(`mongodb://${process.env.DB_USER}:${process.env.DB_PASSWORD}@db:27017/${process.env.DB_NAME}`, {useNewUrlParser: true});

resulting in this error

MongoParseError: Unescaped slash in userinfo section

Then I escaped all the slashes

mongoose.connect(`mongodb:\\/\\/${process.env.DB_USER}:${process.env.DB_PASSWORD}@db:27017\\/${process.env.DB_NAME}`, {useNewUrlParser: true});

and I ended up in this error which I'm not able to solve

MongoParseError: Invalid connection string

This is my index.js

'use strict';

const mongoose = require('mongoose');
mongoose.connect(`mongodb:\\/\\/${process.env.DB_USER}:${process.env.DB_PASSWORD}@db:27017\\/${process.env.DB_NAME}`, {useNewUrlParser: true});

var db = mongoose.connection;
db.on('error', console.error.bind(console, 'connection error:'));
db.once('open', function() {
  console.log('ok');
});

const express = require('express');

// Constants
const PORT = 8080;
const HOST = 'localhost';

var kittySchema = new mongoose.Schema({
  name: String
});

var Kitten = mongoose.model('Kitten', kittySchema);

var silence = new Kitten({ name: 'Test1234' });

// App
const app = express();
app.get('/', (req, res) => {
  res.send(silence.name);
});

app.listen(PORT);
console.log(`Running on http://${HOST}:${PORT}`);

And here's the docker-compose.yml

version: '3.7'
services:
  web:
    build: .
    command: npm start
    volumes:
      - ./src:/usr/app/
      - ./src/node_modules:/usr/app/node_modules
    ports:
      - 80:8080
    environment:
      DB_NAME: websitedb
      DB_USER: /run/secrets/db_user
      DB_PASSWORD: /run/secrets/db_password
    secrets:
      - db_user
      - db_password
  db:
    image: mongo:latest
    volumes:
      - ./mongo-init.js:/docker-entrypoint-initdb.d/mongo-init.js:ro
    ports:
      - "27017:27017"
    environment:
      MONGO_INITDB_DATABASE: websitedb
      MONGO_INITDB_ROOT_USERNAME_FILE: /run/secrets/db_user
      MONGO_INITDB_ROOT_PASSWORD_FILE: /run/secrets/db_password
    secrets:
      - db_user
      - db_password
volumes:
  mongo:
secrets:
  db_user:
    file: ./db_user.txt
  db_password:
    file: ./db_password.txt

Can anyone tell me what I'm doing wrong?


texts: Replication error masked by sp_MSdetect_nonlogged_shutdown returning 'String or binary data would be truncated'

texts:
One of our replication jobs is failing on step 3, which runs sp_MSdetect_nonlogged_shutdown, returns a 'String or binary data would be truncated'. Which of course led us down the path of trying to find a problem in the databases. However we compared the two databases and they are identical. From what I can tell the error is actually a bug in sp_MSdetect_nonlogged_shutdown.
Eventually I checked the job again and step 2 was also failing and throwing a mini dump. When we looked at the dump it says:

the thread tried to read from or write to a virtual address for which it does not have the appropriate access

The id running the job is a SQL Id that is sysadmin, the step (2) is running under the agent service account. The service account has access to the snapshot directory. We have multiple other replication jobs using the same security setup that are working fine.
SQL Version:

Microsoft SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64)
Dec 31 2019 22:39:35
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

Anyone have any idea where I can find the permissions error? Or steps I can use to start narrowing it down?


texts:
What MySQL settings improve text searches?
Which resources (memory, cpus, disk: ssd) to add first to increase performance?

Setup

Amazon RDS

type: db.t2.medium (2 CPU; 4G mem)
MySQL 5.6.27

Application: Redmine 3.3.0
Response time with simple but very generic search (for example: 'the') with no other load: ~15 sec.
Not using  InnoDB FULLTEXT Indexes
Info on Redmine DB:
[--] Database: redmine
[--]  +-- TABLE: 81
[--]  +-- COLL : 3 (utf8_general_ci, utf8_unicode_ci, latin1_swedish_ci)
[--]  +-- ROWS : 5114804
[--]  +-- DATA : 1.3G(81.79%)
[--]  +-- INDEX: 288.5M(18.21%)
[--]  +-- TOTAL: 1.5G
[--]  +-- ENGIN : 1 (InnoDB)

MySQL Settings

binlog_cache_size: 32768
binlog_format: MIXED
bulk_insert_buffer_size: 64000000
default_storage_engine: InnoDB
explicit_defaults_for_timestamp: 1
gtid-mode: OFF
innodb_buffer_pool_size: {DBInstanceClassMemory*3/4}
innodb_file_per_table: 1
innodb_flush_method: O_DIRECT
innodb_ft_cache_size: 80000000
innodb_log_buffer_size: 8388608
innodb_log_file_size: 134217728
key_buffer_size: 16777216
local_infile: 1
log_output: TABLE
log_slave_updates: 1
master-info-repository: TABLE
max_binlog_size: 134217728
max_connections: {DBInstanceClassMemory/12582880}
max_heap_table_size: 512000000
performance_schema: 0
query_cache_limit: 512000
query_cache_size: 512000000
query_cache_type: 1
read_buffer_size: 262144
read_rnd_buffer_size: 524288
relay_log_info_repository: TABLE
relay_log_recovery: 1
sync_binlog: 1
table_open_cache_instances: 16
thread_stack: 262144
tmp_table_size: 512000000


texts: Is there a concept of file locking In SQLite?

texts:
I am using five threads to insert data into a SQLite table, and five threads to read data from the same table. I am not getting any locking error.

Is there any file locking / table locking concept in SqLite?


texts: Can we use TDE as an anti-injection technique

texts:
TDE prevent unauthorized access to the data by restoring the files to another server, because it requires the original encryption certificate and master key.

If i applied a table level encryption using TDE to my database, and am accessing the database from my windows application, through proper authentication. Consider that an intruder is trying some injection technique, my question is that whether he receives the original data or the encrypted one? or can we use TDE as an anti-injection technique?
The query from my application may looks like:

Dim myQuery="Select * from myTable where some_id='" & txtUserId.Text & "'

so that they can inject sql by giving the text like sameValue' or 1=1 or ' will give the whole data. in this case whether he get the encrypted data or be the actual data

Note: i don't use such queries in my application, am using parameterized query and sp throughout my application. asking this for just clarification.